CODEWITHSUNDEEP
mysqlsql
Mayank Bhatnagar
Mayank Bhatnagar

Reputation: 1346

Check value from whole table that match where clause in sql

I am trying to fetch data from table where there are multiple column, but i want to get only those rows which have both code represented with single name. Like

  customer_id     code   
  ct001           abc
  ct002           xyz
  ct001           xyz
  ct003           abc

here no value is unique or primary key, now query should return only ct001 as it has both codes present i.e. abc & xyz .

how to achieve this right now i am trying using this query. 

$sql = "SELECT *  FROM code_table WHERE code IN ('abc' ,'xyz') 
        AND customer_id= '" .$row['customer_id'] . "'";

i am getting customer id from another table and using this query in loop

Upvotes: 1

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

I think you want:

SELECT code 
FROM code_table
WHERE code IN ('abc', 'xyz') 
GROUP BY code
HAVING COUNT(*) = 2;

You should not be doing loops in application code when you can let the database do the work for you.

Upvotes: 2

Related Questions