Joining Tables with Multiple Matches

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

General SQL Server Forums

New to SQL Server Programming

Joining Tables with Multiple Matches

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-07-27 : 14:13:24

Hi All - I have the following tables:

Table #1 - Orders

OrderNum : OrderType
12346789 - Customer
12346790 - Customer
12346791 - Business
12346792 - Government

Table #2 - OrderTypes

OrderType - Region
Customer - NorthWest
Customer - SouthWest
Customer - NorthWest
Business - SouthEast
Business - NorthWest
Business - NorthEast
Government NorthEast
Government SouthEast

I'm joining the tables based on (obviously) Order Type. Although there are multiple entries in the second table related to my JOIN field, I only want it to join and display ONLY the first available record it can find a match on, so that it displays the following:

OrderNum : OrderType : OrderRegion
12346789 - Customer : NorthWest
12346790 - Customer : NorthWest
12346791 - Business : SouthEast
12346792 - Government: NorthEast

The syntax below still displays duplicate order numbers with multiple regions. What is wrong with my syntax that's causing this?

SELECT DISTINCT T1.OrderNum, T1.OrderType, T2.OrderRegion from ORDERS T1
LEFT JOIN OderTypes T2
ON T1.ORDERTYPE = T2.ORDERTYPE

THANKS!

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2011-07-27 : 16:32:11

Your design is not correct.. you have many to many join... I guess work around would be to create identity column and use MIN() or use CTE.

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-07-27 : 16:37:47

The first table has a primary key (OrderNum).
It's the second table that has duplicate values on the join column.
So really, it's one-to-many.

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2011-07-27 : 17:03:04

quote:
Originally posted by funk.phenomena

The first table has a primary key (OrderNum).
It's the second table that has duplicate values on the join column.
So really, it's one-to-many.

You are joining using OrderType which is not using PK so where is that one-to-many...

Insert into Orders
Select '12346789', 'Customer'
union all
Select '12346790', 'Customer'
union all
Select '12346791', 'Business'
union all
Select '12346792', 'Government'

Insert into OrderType
Select 'Customer', 'NorthWest'
union all
Select 'Customer', 'SouthWest'
union all
Select 'Customer', 'NorthWest'
union all
Select 'Business', 'SouthEast'
union all
Select 'Business', 'NorthEast'
union all
Select 'Government', 'NorthEast'
union all
Select 'Government', 'SouthEast'

Select a.OrderNum, min(a.OrderType), min(b.Region) from Orders a
join OrderType b
on a.OrderType = b.OrderType
Group By a.OrderNum

With CTE as
(SELECT ROW_NUMBER() OVER ( ORDER BY CURRENT_TIMESTAMP ) RowNum
, OrderType, Region
from OrderType)

Select a.OrderNum, min(a.OrderType), min(b.Region) from Orders a
join CTE b
on a.OrderType = b.OrderType
Group By a.OrderNum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources