Help with joining two tables

1

Hello,
I have a project that requires me to look at two tables an generate one output.
I have a product table that has the name off all the products available.
I have another table that is use to record what products are available based on company.

** PRODUCT TABLE **
ID PRODUCT_NAME
1 WHEY RO
2 UF
9 WHEY CONCENTRATE
10 WHEY XDS

** COMPANY TABLE **
ID COMPANY PRODUCT
1 1 1
2 1 2
3 1 9
4 1 10
5 10 1
6 10 2
7 10 10

What I'm trying to do is search for a company under the Company Table (e.x. #10)
Retrieve the values from Product Table and join them.
The problem I'm having is that since the Company Table only has three rows of data for company #10, it will not retrieve all of the data from Product Table.

SELECT products.id, products.product_name, if(products.id = company_products.product, "check","") as selected FROM products LEFT JOIN company_products ON products.id = company_products.product WHERE company_products.company = 10

2

Not sure what RDBMS this is (SQL Server has no IF scalar function (it does have IIF))

To get it to return all products, you will need to modify your ON clause. WHERE filters rows from the output, and Company = 10 will not return rows where there are no matches, In the ON clause it will simply limit the rows joined

SELECT products.id, products.product_name,
if(products.id = company_products.product, "check","") as selected
FROM products
LEFT JOIN company_products
ON products.id = company_products.product
AND company_products.company = 10

3

Thank you that work just fine..Very much appreciated been racking my brain over it for a few hours, I haven't work on SQL for a long long time..

1 Like