Joining tables with/without join values

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

SQL Server Development (2000)

Joining tables with/without join values

Author

Topic

returnofthemack
Starting Member

16 Posts

Posted - 2003-01-06 : 12:57:43

I am struggling to get a query going that will join several tables together when one of the tables may or may not have records that allow a join. It seems logical that there is a simple solution, but I have not been able to arrive at one. I've tried left and full join solutions, but to no avail. Here is a simplified version of what I'm working with:

I have three tables (Products,Features,FeatureTypes) and am trying to pull all products, regardless if they have any feature records. I am able to do so if only dealing with the products and features tables, but run into problems when trying to retrieve the featuretype name from the third table. Here is a simplified version of my tables:

PRODUCTS
ProductID - int - identity
Name - varchar(30)
FeatureID - int

FEATURES
FeatureID - int -identity
Name - varchar(30)
FeatureTypeID - int

FEATURETYPES
FeatureTypeID - int - identity
Name - varchar

I am able to get a full list of products using:
select p.name
from products p left join features f on p.featureid = f.featureid

But when I attempt to join the third table (to retrieve the feature type name), it restricts the records being returned. I have tried several solutions, one of which is listed below:

select p.name,ft.name from
products p left join features f on p.featureid = f.featureid
join featuretypes ft on f.featuretypeid = ft.featuretypeid

Please advise!

Thanks in advance for your help...

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-01-06 : 13:05:11

select p.name, ft.name
from products p left join (
features f join featuretypes ft on f.featuretypeid = ft.featuretypeid ) f on p.featureid = f.featureid

Jonathan
{0}

returnofthemack
Starting Member

16 Posts

Posted - 2003-01-06 : 17:38:17

Thanks Jonathan - the solution you provided works like a charm! (just a note that I needed to remove the "f" after the end parenthesis ")" to make it work - see below). Thanks again!

select p.name, ft.name
from products p left join (
features f join featuretypes ft on f.featuretypeid = ft.featuretypeid ) on p.featureid = f.featureid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources