Solved

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

Posted on 2010-11-16
11
387 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
Comment Utility
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 25

Expert Comment

by:Shaun Kline
Comment Utility
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 25

Expert Comment

by:Shaun Kline
Comment Utility
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
 
LVL 2

Expert Comment

by:MKnauer
Comment Utility
Yes Shaun,

you're right.

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

Author Comment

by:Jamie
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Assisted Solution

by:MKnauer
MKnauer earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 25

Accepted Solution

by:
Shaun Kline earned 400 total points
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now