user3242861 user3242861 - 5 months ago 7
SQL Question

Mysql - Inner Join - get the non associate results

i need to do a query.
For example, i have products and sub products. Sub products can be associate to products, so i need a query that give me the results of sub products that are not associate to a certain product.
I did a inner join but is not right.

SELECT *
FROM subProducts
INNER JOIN products on subProducts.id != products.idSubProduct
WHERE products.id = $idProduct


Thank's

Answer

Use an outer join instead with a null check:

SELECT * 
FROM subProducts 
     LEFT JOIN products on subProducts.id = products.idSubProduct 
         AND products.id = $idProduct
WHERE products.id IS NULL

There are other ways to do this as well -- not in and not exists can work similarly. Here's using not exists:

SELECT *
FROM subProducts s
WHERE NOT EXISTS (
    SELECT 1
    FROM products p
    WHERE p.idSubProduct = s.id AND p.id = $idProduct
    )
Comments