Muli-Table Join? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Muli-Table Join?

Author

Topic

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2010-02-12 : 10:54:00

1) I have three tables: tb_analytics, tb_restaurants, and tb_attractions.

2) tb_restaurants and tb_attractions have a similar field structure

3) I want to JOIN the three tables to retrieve the ID from the analytics table and the item name from the other tables

4) The common field (I forget the proper term) is f_analyticassignment in tb_analytics and f_itemGUID in the other two tables

I get close to what I want with the following query:

quote:


SELECT an.f_analyticID, re.f_itemname, at.f_itemname 
FROM tb_analytics an 
LEFT JOIN tb_restaurants re 
ON an.f_analyticassignment = re.f_itemGUID 
LEFT JOIN tb_attractionsamenities at 
ON an.f_analyticassignment = at.f_itemGUID

However, the results look like this:

quote:


f_analyticID	f_itemname	f_itemname
158		NULL		Maritime Museum
159		NULL		Maritime Museum
160		NULL		Maritime Museum
191		NULL		Maritime Museum
168		NULL		Maritime Museum
189		NULL		American Eagle
175		Boston Market	NULL
176		Boston Market	NULL
177		Boston Market	NULL

I would like to see the results like this, if possible:

quote:


f_analyticID	f_itemname
158		Maritime Museum
159		Maritime Museum
160		Maritime Museum
191		Maritime Museum
168		Maritime Museum
189		American Eagle
175		Boston Market
176		Boston Market
177		Boston Market

Any advice you can offer is appreciated.

Thanks,
Matt

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-12 : 11:03:45

[code]
SELECT an.f_analyticID, ISNULL(re.f_itemname, at.f_itemname)
FROM tb_analytics an
LEFT JOIN tb_restaurants re
ON an.f_analyticassignment = re.f_itemGUID
LEFT JOIN tb_attractionsamenities at
ON an.f_analyticassignment = at.f_itemGUID
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 11:03:53

[code]
SELECT an.f_analyticID, re.f_itemname
FROM tb_analytics an
LEFT JOIN(SELECT f_itemGUID,f_itemname FROM tb_restaurants
UNION
SELECT f_itemGUID,f_itemname FROM tb_attractionsamenities )re
ON an.f_analyticassignment = re.f_itemGUID
[/code]

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

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2010-02-12 : 11:18:34

Thank you to you both, as both queries give me the desired results. However, it seems that the one using the UNION will make it easier on me, as I can add an "ORDER BY f_itemname" argument without getting an "ambiguous column name" error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 11:21:05

quote:
Originally posted by mattboy_slim

Thank you to you both, as both queries give me the desired results. However, it seems that the one using the UNION will make it easier on me, as I can add an "ORDER BY f_itemname" argument without getting an "ambiguous column name" error

you wont get the error provided you do aliasing as below


ISNULL(re.f_itemname, at.f_itemname) AS f_itemname
....

ORDER BY f_itemname

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

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2010-02-12 : 11:29:46

Oh, OK, I see how that works. Are there any disadvantages or advantages to using either method?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources