Link to home
Start Free TrialLog in
Avatar of Jamie
JamieFlag for United Kingdom of Great Britain and Northern Ireland

asked on

MSSQL Joins - how to join 3 tables and return all rows even if null

Hi All,

I'm getting in a right muddle trying to work this out, hence my need for your help.

I have 3 tables:

1. Products ~ List of products
2. Categories ~ List of category names
3. ProCategories ~ List of products (1) in which category (2) - this holds only id numbers.

I need t-sql script, which will be used via asp to list all products and the categories they are in - and also the categories they are not in - including the category name, which can only be obtained from table 2. I want to be able to filter by product name also.

My feeble effort so far;

select p.cname, c.catdescription  from products as p,categories as c, prodcategories as pc
where pc.intcatalogid = p.catalogid
and   pc.intcategoryid = c.categoryid
and p.cname like '%yellow%'

Looking for something like

Product         Category       ProdCategory
Apple            Fruit               Exists
Apple            Green            Exists
Apple            Vegetable     NULL                

Many thanks

Jamie



 
Avatar of MKnauer
MKnauer
Flag of Afghanistan image

untested, but should work
select 
		p.cname
		, isnull(c.catdescription , 'Not Exist')
	from products as p
		left join prodcategories as pc on pc.intcatalogid  = p.catalogid
		left join categories     as c  on pc.intcategoryid = c.categoryid
	where p.cname like '%yellow%'

Open in new window

Avatar of Shaun Kline
select p.cname, c.catdescription, case when pc.intcategoryid is null then 'Not Exists' else 'Exists' end as ProdCategory
from products as p
    cross join categories as c
    left outer join prodcategores as pc on p.catalogid = pc.intcatalogid
       and c.categoryid = pc.intcategoryid
       and p.cname like '%yellow%'
Had a misspelling in the one of the table names:
select p.cname, c.catdescription, case when pc.intcategoryid is null then 'Not Exists' else 'Exists' end as ProdCategory
from products as p
    cross join categories as c
    left outer join prodcategories as pc on p.catalogid = pc.intcatalogid
       and c.categoryid = pc.intcategoryid
       and p.cname like '%yellow%'

Open in new window

Yes Shaun,

you're right.

Missed the second part of the question... :-(
Avatar of Jamie

ASKER

Hi both.

Many thanks for your quick replies. Apologies, I was not 100% clears in what I needed.
If I have a 100 category names, so need the list of product name with category names and if it exists in the prodcategory table - so each product should return 100 rows, with either an exists or not exists (null).

MKnauer: Your script filters correctly.
Shaun_Kline: Your script is not filtering on '%yellow%' for some reason?

Regards

Jamie
SOLUTION
Avatar of MKnauer
MKnauer
Flag of Afghanistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Well I should've double checked it...
select 
	p.cname
	, c.catdescription
	, case when ( c.categoryid = pc.intcategoryid) then 'Exists' else 'Not Exists' end as ProdCategory
from products as p
    left outer join prodcategories as pc on p.catalogid = pc.intcatalogid and c.categoryid = pc.intcategoryid
    cross join categories as c
where p.cname like '%yellow%'

Open in new window

In case anyone is interested:
The same script against Adventureworks Sample DB:

Cheers
Martin
SELECT  
		  E.[EmployeeID]
		, E.[LoginID]
		, EDH.EmployeeID
		, EDH.DepartmentID
		, D.DepartmentID
		, D.Name
		, case when ( EDH.DepartmentID =  D.DepartmentID) THEN 'Yes' ELSE 'NO' END AS [Exist]
	FROM [AdventureWorks].[HumanResources].[Employee] E
		LEFT JOIN [AdventureWorks].[HumanResources].[EmployeeDepartmentHistory] EDH ON E.EmployeeID = EDH.EmployeeID
		Cross JOIN [AdventureWorks].[HumanResources].[Department] D                  
	-- where E.[LoginID] like '%ruth%'
	order by E.[EmployeeID]
		, EDH.DepartmentID
		, D.DepartmentID

Open in new window

Avatar of Jamie

ASKER

Hi MKnauer: Thanks for your replies;

16/11/10 04:15 PM, ID: 34146714 ~ returns exists for all 100 rows if product belongs to one category or more?

16/11/10 04:18 PM, ID: 34146748 ~ SQL Server Database Error: The multi-part identifier "c.categoryid" could not be bound. ~ Line 6

thanks

Jamie

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jamie

ASKER

Hi Shaun_Kline - Works perfectly, exactly as I needed, many thanks for your help.

MKnauer  - you were very close to cracking it!

thanks

Jamie