CODEWITHSUNDEEP
sqlsql-serverjoin
Kevat Shah
Kevat Shah

Reputation: 159

Join information from 2 tables

I currently have 2 tables which hold information about an item. The first table shows various names for a given item no:

Item No   Name
1         Name1
1         Name2
1         Name3

The second table shows various locations for the given item no:

Item No  Location
1        Location1
1        Location2

I'm currently using the following statement to join these 2 tables

Select * 
from FirstTable a
Left Join 
(
   Select * 
   from SecondTable
) b 
   on a.itemno = b.itemno

I end up with this data:

Item No    Name       Location
1          Name1      Location1
1          Name1      Location2
1          Name2      Location1
1          Name2      Location2
1          Name3      Location1
1          Name3      Location2

What I would like to end up with is this:

Item No    Name       Location
1          Name1      Location1
1          Name2      Location2
1          Name3      NULL

How can I achieve the results I'm looking for?

Upvotes: 0

Views: 92

Answers (1)

Taryn
Taryn

Reputation: 247860

How about using something like this. This assigns generic row_number() to each value in the table and then you join on that row number value:

select i.[item no],
  i.name,
  l.location
from
(
  select [item no], name,
    row_number() over(partition by [item no] order by name) rn
  from item
) i
full outer join
(
  select [item no], location,
    row_number() over(partition by [item no] order by location) rn
  from locations
) l
  on i.rn = l.rn

See SQL Fiddle with Demo

Result from your sample data is:

| ITEM NO |  NAME |  LOCATION |
-------------------------------
|       1 | Name1 | Location1 |
|       1 | Name2 | Location2 |
|       1 | Name3 |    (null) |

Upvotes: 1

Related Questions