Joining Multiple Tables - SQL Server Forums

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 Multiple Tables

Author

Topic

MiffySnow
Starting Member

2 Posts

Posted - 2012-05-22 : 17:56:52

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-05-22 : 18:02:58

Your query looks fine. You have more rows because some of the IDs map to multiple rows.

The only comment I have is make sure that LEFT JOIN is what you want. You may only need JOIN, which is shorthand for INNER JOIN. LEFT JOIN is shorthand for LEFT OUTER JOIN. Make sure you understand the difference between INNER and OUTER joins.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-23 : 03:12:31

quote:
Originally posted by tkizer

Your query looks fine. You have more rows because some of the IDs map to multiple rows.

The only comment I have is make sure that LEFT JOIN is what you want. You may only need JOIN, which is shorthand for INNER JOIN. LEFT JOIN is shorthand for LEFT OUTER JOIN. Make sure you understand the difference between INNER and OUTER joins.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

+1. The Left JOIN might be the culprit.
It might be bringing more data than you are expecting from one of the tables.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-23 : 03:40:02

LEFT JOIN is unlikely the issue here. The additional 3 rows could be due to you have multiple same ID in the tblFood or tblColour or tblDrink table as Tara has mention

You can verify this by


select FoodID
from   tblFood
group by FoodID
having count(*) > 1

KH
[spoiler]Time is always against us[/spoiler]

MiffySnow
Starting Member

2 Posts

Posted - 2012-05-23 : 04:42:43

Thank you so much for all your help. I hadn't realised that LEFT JOIN stood for LEFT OUTER JOIN, I think this is what I need in this case but is certainly something to think about in other queries, which have been going squiffy!

KH's query was really helpful and I was able to locate some multiple IDs, leading to the additional rows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-23 : 15:56:46

[code]
SELECT ClientID,IDVal,CatVal
INTO #Temp
FROM tblA t
UNPIVOT(IDVal FOR CatVal IN (FoodID,ColourID,DrinkID))u

SELECT ClientID,
MAX(CASE WHEN CatVal = 'FoodID' THEN Val END) AS FoodID,
MAX(CASE WHEN CatVal = 'ColourID' THEN Val END) AS ColourID,
MAX(CASE WHEN CatVal = 'DrinkID' THEN Val END) AS DrinkID
FROM
(
SELECT t.ClientID,t.CatVal,f.Food AS Val
FROM #Temp t
INNER JOIN tblFood f
ON f.FoodID = t.IDVal
WHERE t.CatVal='FoodID'

UNION ALL

SELECT t.ClientID,t.CatVal,c.Color
FROM #Temp t
INNER JOIN tblFood c
ON c.ColourID = t.IDVal
WHERE t.CatVal='ColourID'

UNION ALL

SELECT t.ClientID,t.CatVal,d.Drink
FROM #Temp t
INNER JOIN tblDrink d
ON d.DrinkID = t.IDVal
WHERE t.CatVal='DrinkID'
)t

DROP TABLE #Temp
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources