Solved

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

Posted on 2010-11-16
11
404 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

820 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