Inner Joining One Record When Multiple Exist

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Inner Joining One Record When Multiple Exist

Author

Topic

Aerathi
Starting Member

14 Posts

Posted - 2006-05-16 : 11:18:03

I'm trying to do an inner join to a table, which sometimes contains multiple records.


Table1
OrderNo            ItemNo
12345                1
12345                2

Table2
OrderNo             ItemNo           VendorID
12345                  1                       578
12345                  1                       1475
12345                  2                       98154

When I join these tables I need it to just return one record for ItemNo 1, and one record for Itemno 2, etc. I'm not really concerned with which record is pulled from table two, the vendor ID is needed but relatively arbitrary.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-05-16 : 11:29:13

Use group by and min or max or correlated query returning top1 vendorID.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-16 : 12:03:55

Hi all,

Aerathi - Just in case mmarovic's answer was a little too succinct for you, I decided to write it out...

--data
declare @Table1 table (OrderNo int, ItemNo int)
insert @Table1
          select 12345, 1
union all select 12345, 2

declare @Table2 table (OrderNo int, ItemNo int, VendorID int)
insert @Table2
          select 12345, 1, 578
union all select 12345, 1, 1475
union all select 12345, 2, 98154

--calculation

--max
select a.OrderNo, a.ItemNo, min(VendorID) as VendorID
from @Table1 a inner join @Table2 b on a.OrderNo = b.OrderNo and a.ItemNo = b.ItemNo
group by a.OrderNo, a.ItemNo

--or min
select a.OrderNo, a.ItemNo, max(VendorID) as VendorID
from @Table1 a inner join @Table2 b on a.OrderNo = b.OrderNo and a.ItemNo = b.ItemNo
group by a.OrderNo, a.ItemNo

--or not fussed
select OrderNo, ItemNo,
    (select top 1 VendorID from @Table2 
    where OrderNo = a.OrderNo and ItemNo = a.ItemNo) as VendorID
from @Table1 a

--or random
select OrderNo, ItemNo,
    (select top 1 VendorID from @Table2 
    where OrderNo = a.OrderNo and ItemNo = a.ItemNo order by newid()) as VendorID
from @Table1 a

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Aerathi
Starting Member

14 Posts

Posted - 2006-05-16 : 14:57:52

Ahh thanks. Very helpful stuff. I kind of had an idea how I should do it but I wasn't quite sure how to implement it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources