Menu
My question is quite similar to Restricting a LEFT JOIN, with a variation.
Assuming I have a table SHOP and another table LOCATION. Location is a sort of child table of table SHOP, that has two columns of interest, one is a Division Key (calling it just KEY) and a "SHOP" number. This matches to the Number "NO" in table SHOP.
I tried this left outer join:
SELECT S.NO, L.KEY FROM SHOP S LEFT OUTER JOIN LOCATN L ON S.NO = L.SHOP but I'm getting a lot of duplicates since there are many locations that belong to a single shop. I want to eliminate them and just get a list of "shop, key" entries without duplicates.
The data is correct but duplicates appear as follows:
SHOP KEY 1 XXX 1 XXX 2 YYY 3 ZZZ 3 ZZZ etc. I would like the data to appear like this instead:
SHOP KEY 1 XXX 2 YYY 3 ZZZ etc. SHOP table:
NO 1 2 3 LOCATION table:
LOCATION SHOP KEY L-1 1 XXX L-2 1 XXX L-3 2 YYY L-4 3 YYY L-5 3 YYY (ORACLE 10g Database)
581
Avoiding Duplicates Again, if we perform a left outer join where date = date, each row from Table 5 will join on to every matching row from Table 4. However, in this case, the join will result in 4 rows of duplicate dates in the joined DataSet (see Table 6).
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.
From what you are saying, the 2 tables you are comparing are more or less the same, and full outer join giving you records from both tables, chances are you are going to get a lot of duplicates. So, that's the logic behind it.
The SQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It does not remove duplicate rows between the various SELECT statements (all rows are returned). Each SELECT statement within the UNION ALL must have the same number of fields in the result sets with similar data types.
You need to GROUP BY 'S.No' & 'L.KEY'
SELECT S.NO, L.KEY FROM SHOP S LEFT OUTER JOIN LOCATN L ON S.NO = L.SHOP GROUP BY S.NO, L.KEY 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
