Having trouble with SQL 'in'

Question

I want to search for all records in product_description who match a certain brand, and also whose status in another table product is '1'. This is how far I've got, but I'm tripping up somewhere:

SELECT brand,
       name,
       product_id
FROM   product_description
WHERE  brand = $brandname
       AND product_id IN (SELECT product_id,
                                 status
                          FROM   product
                          WHERE  status = '1'); 

Thanks in advance

Answer 1

Reason:

Inner query should return only 1 column if you want to use it with IN.

Solution:

Remove status from the inner query. Also use single quotes ' around the variable $brandname:

$sql = "SELECT brand,name,product_id 
        FROM product_description 
        where brand = '$brandname'
              and product_id in 
                    (SELECT product_id FROM product where status='1')";

Answer 2

No need to fetch status in the sub query. Try with this -

$sql = "SELECT 
           brand,name,product_id 
        FROM product_description 
        WHERE brand ='$brandname' 
        AND product_id in (SELECT product_id FROM product where status='1')";

Answer 3

Need to update your query as

"SELECT brand,name,product_id FROM product_description where brand 
= '$brandname' and product_id in (SELECT product_id FROM product where 
status='1')";

1. You need to enclose your variable $brandname within single quotes if its not an integer
2. Your sub-query is returning multiple values so you need to select only product_id

Answer 4

I can't say for definite without an error or an output of the query, but I think you should not be selecting "status" in the subquery, as you are checking if a specific product_id is within a result set of both a product_id and a status. Try rewriting the subquery so that only product_id is selected.

Answer 5

Join your tables.

$sql = "SELECT d.brand, d.name, d.product_id
FROM   product_description AS d
JOIN   product AS p ON d.product_id = p.product_id AND p.status = '1'
WHERE  d.brand = '$brandname'";

Best practise is to use prepared statements