22

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)

Community
  • 1
  • 1
KG -
  • 7,130
  • 12
  • 56
  • 72
  • You shouldn't be getting any duplicates, though, as you stated, you may get more than one key for a shop if you have more than one location record for the shop. Please explain or give an example of what you mean by "duplicate". – Marcus Adams Mar 28 '10 at 15:12
  • 1
    @Marcus I thought the same at first but I'm assuming that multiple locations can have the same division key. – Martin Smith Mar 28 '10 at 15:14
  • @Marcus & Martin: ah i don't think i made this clear. Yes Multiple locations can and do have a same division key. (Strictly speaking divnkey is the parent of shop. So hierarchy goes like this Divnkey > Shop > location). I'm trying to populate Shop's table with the appropriate division key data. May sound weird, but it's a one time process and i was trying to generate update scripts for table SHOP from the table LOCATION's data - through a select 'update shop set divnkey = ....' command. Didn't want to complicate the question so put a simple select. – KG - Mar 28 '10 at 16:06
  • Sorry only just saw your response. Will update my answer again. – Martin Smith Mar 28 '10 at 20:15

3 Answers3

31

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
SoftwareGeek
  • 15,234
  • 19
  • 61
  • 78
  • 1
    +1 for using `GROUP BY` which is equivalent to `DISTINCT` on Oracle http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:32961403234212 but can sometimes be faster on other engines e.g. Postgres (using hashes instead of sorting.) – vladr Mar 28 '10 at 23:45
  • Ok you just made me feel reaally stupid. This works perfectly. Thanks. But any additional comments on why the group by is required at all when the left outer is supposed to take care of this. As in i'm trying to understand how the left outer really works. – KG - Mar 29 '10 at 04:17
  • @Kaushik: A LEFT OUTER JOIN is not equivalent to nor does it imply a GROUP BY or DISTINCT. The LEFT JOIN takes all rows from the left (first) table, and joins in all rows from the right (second) table where the join condition is satisfied. In an left *outer* join, if there is no data found in the right table which matches data from the left table the left-table data is still returned with NULLs put in for all right-table data. No grouping is expressed or implied. Personally I'd have used DISTINCT as I think it more clearly specifies intent, but YMMV. – Bob Jarvis - Слава Україні Mar 29 '10 at 12:22
  • @Bob Thanks for clarifying. I misunderstood what was meant by a LEFT OUTER JOIN. I presumed it to be different from a normal LEFT JOIN. Now i understand them to be the same? For the interwebs: A helpful link by Jeff A on understanding the whole join concept: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html – KG - Mar 30 '10 at 06:22
  • 1
    @Kaushik - yes, LEFT JOIN and LEFT OUTER JOIN are equivalent because the OUTER keyword is optional. If you specify FULL, LEFT, or RIGHT as the join type you can skip specifying OUTER, but I usually put it in because I think it helps make the code clearer. (I know - I didn't put OUTER into my earlier comment - but, um, I was trying to fit everything into the 600 character limit! Yeah, that's the ticket..! :-) – Bob Jarvis - Слава Україні Mar 31 '10 at 18:03
  • I really hope this doesn't start a fashion for using group by instead of distinct. Isn't the postgres issue pre-8.1? – David Aldridge Dec 11 '12 at 18:58
  • You cannot imagine how you saved my day , thank you, +++ to you – B.S. Dec 04 '13 at 12:27
7

I had this problem too but I couldn't use GROUP BY to fix it because I was also returning TEXT type fields. (Same goes for using DISTINCT).

This code gave me duplicates: 

select mx.*, case isnull(ty.ty_id,0) when 0 then 'N' else 'Y' end as inuse 
from master_x mx 
left outer join thing_y ty on mx.rpt_id = ty.rpt_id

I fixed it by rewriting it thusly:

select mx.*, 
case when exists (select 1 from thing_y ty where mx.rpt_id = ty.rpt_id) then 'Y' else 'N' end as inuse
from master_x mx

As you can see I didn't care about the data within the 2nd table (thing_y), just whether there was greater than zero matches on the rpt_id within it. (FYI: rpt_id was also not the primary key on the 1st table, master_x).

aLearner
  • 1,051
  • 14
  • 30
woodge
  • 91
  • 1
  • 4
7

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)
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    Nice :) This is why it's important to get more than one angle. Thanks Martin, your solution most definitely satisfies my underlying requirement (But the other answer specifically takes care of removing the duplicates, so going to have to mark that one). Cheers – KG - Mar 29 '10 at 04:19