MSSQL Joins - how to join 3 tables and return all rows even if null
Posted on 2010-11-16
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