Solved

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

Posted on 2010-11-16
11
418 Views
Last Modified: 2012-05-10
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



 
0
Comment
Question by:Jamie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 2

Expert Comment

by:MKnauer
ID: 34146154
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

0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 34146234
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%'
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 34146249
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

0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 2

Expert Comment

by:MKnauer
ID: 34146395
Yes Shaun,

you're right.

Missed the second part of the question... :-(
0
 

Author Comment

by:Jamie
ID: 34146483
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
0
 
LVL 2

Assisted Solution

by:MKnauer
MKnauer earned 100 total points
ID: 34146714
So please try this small modification from Shaun's Code
select 
	p.cname
	, c.catdescription
	, case when pc.intcategoryid is null then 'Not Exists' else 'Exists' end as ProdCategory
from products as p
    left outer join prodcategories as pc on p.catalogid = pc.intcatalogid 
    cross join categories as c
where p.cname like '%yellow%'

Open in new window

0
 
LVL 2

Expert Comment

by:MKnauer
ID: 34146748
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

0
 
LVL 2

Expert Comment

by:MKnauer
ID: 34146813
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

0
 

Author Comment

by:Jamie
ID: 34146919
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

0
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 400 total points
ID: 34147245
Correction on my code.
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
WHERE p.cname like '%yellow%'

Open in new window

0
 

Author Closing Comment

by:Jamie
ID: 34147309
Hi Shaun_Kline - Works perfectly, exactly as I needed, many thanks for your help.

MKnauer  - you were very close to cracking it!

thanks

Jamie
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question