Remove Duplicates from LEFT OUTER JOIN

Question

Hey folk my question is quite similar to http://stackoverflow.com/questions/757957/restricting-a-left-join

I have a variation in that request though and the comment didn't allow too many characters hence posting as a new question. I hope this doesn't go against the posting rules/etiquette.

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. any ideas how?

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 as:

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   

(edit: ORACLE 10g Database)

Answer 1

EDIT Following the update in your scenario

I think you should be able to do this with a simple sub query (though I haven't tested this against an Oracle database). Something like the following

UPDATE shop s
SET divnkey = (SELECT DISTINCT L.KEY FROM LOCATN L WHERE S.NO = L.SHOP)

The above will raise an error in the event of a shop being associated with locations that are in multiple divisions.

If you just want to ignore this possibility and select an arbitrary one in that event you could use

UPDATE shop s
SET divnkey = (SELECT MAX(L.KEY) FROM LOCATN L WHERE S.NO = L.SHOP)

Answer 2

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