CODEWITHSUNDEEP
phpmysqlsqlwhere-clause
themartin
themartin

Reputation: 85

Having trouble with SQL 'in'

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

Upvotes: 1

Views: 72

Answers (5)

Sougata Bose
Sougata Bose

Reputation: 31749

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')";

Upvotes: 1

venca
venca

Reputation: 1222

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

Upvotes: 0

Oliver Nicholls
Oliver Nicholls

Reputation: 1431

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.

Upvotes: 0

Narendrasingh Sisodia
Narendrasingh Sisodia

Reputation: 21437

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

Upvotes: 1

Raging Bull
Raging Bull

Reputation: 18747

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')";

Upvotes: 2

Related Questions