CODEWITHSUNDEEP
mysqlsql
Dzhuneyt
Dzhuneyt

Reputation: 8701

Get only results from table "products" if they have entries in two other tables (identified by ID and product type columns)

I have a table with "products" that stores a product ID and product type (string).

I'd like to retrieve all those "products", joining them to two other tables that list two individual products and ignore all other products that are not found in these two tables. I have the "product_type" inside the "products" table.

Here's a sample structure:

  1. Table products (ID, idProduct, type)

    1|3|tv

    2|4|tv

    3|13|phone

  2. Table tvs (ID, name)

    3|Sony

    4|Phillips

  3. Table phones (ID, name)

    13|Samsung Galaxy

Even if there are duplicate idProduct values in products we are identifying/joining by also checking the type column. Currenly, my approach using LEFT JOIN to both tables does not work correctly, because it doesn't return any values in case proucts contains only one type of product.

Upvotes: 0

Views: 49

Answers (2)

537mfb
537mfb

Reputation: 1482

I suggest using a union - so you can get data from the other tables rather then just products table:

(SELECT p.ID, p.idProduct, p.type, t.name
FROM products p, tvs t
WHERE p.idProduct=t.ID AND p.type = 'tv')
UNION
(SELECT p.ID, p.idProduct, p.type, t.name
FROM products p, phones t
WHERE p.idProduct=t.ID AND p.type = 'phones')

Upvotes: 1

Dzhuneyt
Dzhuneyt

Reputation: 8701

SELECT * FROM products p
LEFT JOIN tvs tvs ON tvs.id=p.idProduct AND p.type="tv"
LEFT JOIN phones phones on phones.id=p.idProduct AND p.type="phone"
WHERE (tvs.id IS NOT NULL OR phones.id IS NOT NULL)

Self answer, but I think others might find it useful.

The trickyp part is the OR in the WHERE condition. We are doing a LEFT JOIN, but we must also check if the product exists in the first OR second table.

Upvotes: 0

Related Questions