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)
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