Solved

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

Posted on 2010-11-16
11
393 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
 
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

867 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

22 Experts available now in Live!

Get 1:1 Help Now