MySQL multiple AND condition from different table

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.

Answer

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'