Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

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



 
0
Jamie
Asked:
Jamie
  • 5
  • 3
  • 3
2 Solutions
 
MKnauerCommented:
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
 
Shaun KlineLead Software EngineerCommented:
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
 
Shaun KlineLead Software EngineerCommented:
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
MKnauerCommented:
Yes Shaun,

you're right.

Missed the second part of the question... :-(
0
 
JamieAuthor Commented:
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
 
MKnauerCommented:
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
 
MKnauerCommented:
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
 
MKnauerCommented:
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
 
JamieAuthor Commented:
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
 
Shaun KlineLead Software EngineerCommented:
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
 
JamieAuthor Commented:
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now