CODEWITHSUNDEEP
sqldb2
Geoff_S
Geoff_S

Reputation: 5107

Joining without duplicate records/result sets

I'm trying to figure out how to join two tables, one of which has essentially duplicate rows, but only return one result set. Basically tableOne has item and category and tableTwo is the only place that has color of the items, but some records in that table have multiple rows for location. There's really no distinction there, it's simply for legacy data purposes.

I just need the color for the item, which will always be the same in tableTwo, but because of the 4 records in table two my below query always returns 4 records where I only want one

tableOne

item | category
---------------
 1        A

tableTwo

item  |  category  |  color  |  location
-----------------------------------------
 1         A           Red        1
 1         A           Red        2
 1         A           Red        3
 1         A           Red        4

Query :

select t1.item,t2.category,t2.color
from tableOne t1
left join tableTwo t2 
    on t1.item = t2.item 
    and t1.category = t2.category

How can I run this to essentially disregard the location and only return one record for the query in DB2?

Upvotes: 1

Views: 32

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

One method uses row_number():

select t1.item, t1.category, t2.color
from tableOne t1 left join
     (select t2.*,
             row_number() over (partition by item, category order by item) as seqnum
      from tableTwo t2 
     ) t2
     on t2.item = t1.item and
        t2.category = t1.category and
        t2.seqnum = 1;

I think you should take category from t1 and not t2, unless you explicitly want a NULL value when there is no match.

Upvotes: 2

GMB
GMB

Reputation: 222482

Use SELECT DISTINCT :

select distinct t1.item,t2.category,t2.color 
from tableOne t1
left join tableTwo t2 on t1.item = t2.item and t1.category = t2.category

Upvotes: 1

Related Questions