MySQL multiple AND condition from different table

Question

I have the following database structure:

product

+---------+-------+--------+
|  name   |  upc  |  model |
+---------+-------+--------+
| Value 1 | 1,234 | phone  |
| Value 2 | 2,345 | tablet |
+---------+-------+--------+

product_import

+-----------+---------------+----------+
| to_import | id_oc_prd_map | category |
+-----------+---------------+----------+
| 1         |       1,234   | apple    |
| 2         |       2,345   | banana   |
+-----------+---------------+----------+

I would like to get rows from product table with multiple conditions:

SELECT *
FROM product
WHERE upc IN (SELECT id_oc_prd_map FROM product_import) 
AND product.model = 'phone' 
AND product_import.category = 'apple'

select items by common key (upc & id_od_prd_map)
model = 'phone'
category = 'apple'

In the example above I should get

+---------+-------+--------+
|  name   |  upc  |  model |
+---------+-------+--------+
| Value 1 | 1,234 | phone  |

as results, but I get error for second AND statement, because it is in another table.

Luuk · Accepted Answer · 2021-04-11 11:23:26Z

1

Just find the upc where category equals 'apple':

SELECT *
FROM product
WHERE upc IN (SELECT id_oc_prd_map FROM product_import WHERE import.category = 'apple') 
AND product.model = 'phone'

or use:

SELECT *
FROM product
INNER JOIN product_import ON product_import.category = 'apple'
                          AND product_import.id_oc_prd_map = product.upc
WHERE product.model = 'phone'

answered Apr 11, 2021 at 11:23

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barbaros Özhan · Accepted Answer · 2021-04-11 11:28:51Z

1

You can use a simple [INNER]JOIN including ON clause to join tables, and WHERE clause to add extra criterias such as

SELECT p.*
  FROM product AS p
  JOIN product_import AS pi
    ON p.upc = pi.id_oc_prd_map
 WHERE p.model = 'phone' 
   AND pi.category = 'apple'

where no need to repeat whole table names but aliases

Demo

answered Apr 11, 2021 at 11:28

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Collectives™ on Stack Overflow

MySQL multiple AND condition from different table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related