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



 
Microsoft SQL ServerASP

Avatar of undefined
Last Comment
Jamie

8/22/2022 - Mon
MKnauer

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

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%'
Shaun Kline

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
MKnauer

Yes Shaun,

you're right.

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

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
MKnauer

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
MKnauer

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
MKnauer

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

ASKER
Jamie

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Jamie

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

MKnauer  - you were very close to cracking it!

thanks

Jamie
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy