Menu
I have the below table. the only difference in data is suff, wt; the rest of them look the same.
Things table
Prefix Baseletter suff CSCcode Description WT BNO BNO-CSCcode
EIGC A5560 BGA 04020 blah1 0 5560 5560-04020
EIGC A5560 HEA 04020 blah2 17.9 5560 5560-04020
Mapp table
BNO BNO-CSCcode EID Description
5560 5560-04020 4005 blah1
5560 5560-04020 4011 blah2
I'm trying to inner join them using BNO-CSCcode to get EID for corresponding BNO. But my query is returning duplicates. I'm getting 4 records, even though the first table only has two records.
My SQL query:
SELECT
Things.Prefix ,
Things.Baseletter,
Things.suff,
Things.CSCcode,
Things.WT,
Mapping.BNO-CSCcode,
Mapping.EID
FROM
Things
INNER JOIN Mapping ON Things.BNO-CSCcode = Mapping.BNO-CSCcode
Why am I getting these duplicates, and how can I fix that?
778
Using an Incomplete ON Condition Unwanted rows in the result set may come from incomplete ON conditions. In some cases, you need to join tables by multiple columns. In these situations, if you use only one pair of columns, it results in duplicate rows.
Solution. Select column values in a specific order within rows to make rows with duplicate sets of values identical. Then you can use SELECT DISTINCT to remove duplicates. Alternatively, retrieve rows in such a way that near-duplicates are not even selected.
The answer is yes, if there are any. If there are duplicate keys in the tables being joined.
Inner join (superheroes, publishers) An inner join returns all rows from x with matching values in y, and all columns from both x and y. If there are multiple matches between x and y, all match combinations are returned.
BNO-CSCcode contains duplicates. You are joining the first record of Things to both records of Mapp, then the second record of Things joins to both records of Mapp. Giving you a total of 4 records.
If you want to join these together, you need some unique way of identifying the rows between the tables.
A Distinct should bring it back down to 2 records, but likely you need to join on a few more fields to get it to 2 records:
SELECT DISTINCT
Things.Prefix,
Things.Baseletter,
Things.suff,
Things.CSCcode,
Things.WT,
Mapping.BNO-CSCcode,
Mapping.EID
FROM
Things
INNER JOIN Mapping ON Things.BNO-CSCcode = Mapping.BNO-CSCcode
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
