CODEWITHSUNDEEP
mysqlsql
Asad Nadeem
Asad Nadeem

Reputation: 549

Specifying "AND" Condition with more than 1 time for same column in MYSQL

I have a table like:

product_id | attribute_id | text
--------------------------------
52         | 16           | 1.0 Inch - 2.9 Inches
52         | 15           | Radio
52         | 14           | Simple Phones
134        | 16           | 1.0 Inch - 2.9 Inches
134        | 15           | Wifi
134        | 14           | Dual SIM Phones

I am writing the query for getting products that are Simple Phones with 1.0 Inch - 2.9 Inches Screen.

I want to place two conditions for one column.

When I am writing the query:

select
      * 
from
      product_attribute 
where 
      (text = '1.0 Inch - 2.9 Inches') 
      and 
      (text = 'Simple Phones')

so I am getting "0 Results". While I am running the following Query:

select
      * 
from
      product_attribute 
where 
      text IN('1.0 Inch - 2.9 Inches','Simple Phones')

then I am getting the following result:

product_id | attribute_id | text
--------------------------------
52         | 16           | 1.0 Inch - 2.9 Inches
52         | 14           | Simple Phones
134        | 16           | 1.0 Inch - 2.9 Inches

But I need only product_id = 52 because this product has both the filters either 1.0 Inch - 2.9 Inches and Simple Phones while product_id = 134 only has 1.0 Inch - 2.9 Inches

Please help me out to sort out this problem.

** Sorry for the English :)

Upvotes: 3

Views: 66

Answers (6)

user613114
user613114

Reputation: 2821

In this specific case I would use self joins:

SELECT pa.*
FROM product_attribute pa,
  product_attribute pa1,
  product_attribute pa2
WHERE pa.product_id = pa1.product_id
AND pa.product_id   = pa2.product_id
AND pa1.text        = '1.0 Inch - 2.9 Inches'
AND pa2.text        = 'Simple Phones';

Advantage of this approach, you can dynamically add conditions for same columns. And I believe it is quite simple to understand. I have not tested this query on my machine, but I am sure it will work for you.

Thanks.

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18747

Use HAVING clause:

select *
from product_attribute 
where text IN('1.0 Inch - 2.9 Inches','Simple Phones')
group by product_id
having count(product_id)=2

See example in SQL Fiddle.

EDIT:

For getting all records:

select *
from product_attribute T1 LEFT JOIN
  (select product_id
   from product_attribute 
   where text IN('1.0 Inch - 2.9 Inches','Simple Phones')
   group by product_id
   having count(product_id)=2) T2 on T1.product_id=T2.product_id
WHERE T2.product_id IS NOT NULL
AND T1.text IN('1.0 Inch - 2.9 Inches','Simple Phones')

Result:

PRODUCT_ID    ATTRIBUTE_ID  TEXT
52            16            1.0 Inch - 2.9 Inches
52            14            Simple Phones

See result in SQL Fiddle.

Upvotes: 2

Sadikhasan
Sadikhasan

Reputation: 18600

Try this

SELECT *
FROM product_attribute
WHERE product_id in
    (SELECT product_id
     FROM product_attribute
     WHERE text IN('1.0 Inch - 2.9 Inches','Simple Phones')
     GROUP BY product_id HAVING count(product_id)=2);

Upvotes: 1

Jason Heo
Jason Heo

Reputation: 10246

You need to execute SELF JOIN:

SELECT *
FROM product_attribute AS t1 INNER JOIN product_attribute AS t2
    ON t1.product_id = t2.product_id
WHERE t1.text = '1.0 Inch - 2.9 Inches' AND t2.text = 'Simple Phones'

Other's answer should returns same result but performance is not same. please try all of answers and accept best answer.

Upvotes: 1

Ivan Cachicatari
Ivan Cachicatari

Reputation: 4284

Your Query is quite complicated but possible:

select * 
from product_attribute 
where product_id in 
(
    select t.filtered_id, t.joined_text from
    ( -- selecting all rows with 
      -- '1.0 Inch - 2.9 Inches','Simple Phones' and concatenate
       select
          product_id as filtered_id, group_concat(`text`) as joined_text
       from
          product_attribute 
       where 
          text IN('1.0 Inch - 2.9 Inches','Simple Phones')
       group by product_id ) as t

    where   -- mysql may concatenate in any order
      t.joined_text = '1.0 Inch - 2.9 Inches,Simple Phones'
      or 
      t.joined_text = 'Simple Phones,1.0 Inch - 2.9 Inches'
)

Upvotes: 1

Saurabh Mishra
Saurabh Mishra

Reputation: 31

Here each row is considered as a separate entity, so even though they are having the same product_id, the 2 matching entries for product_id 52 would be considered differently. Better to group by the rows on product_id and then apply the in condition

Eg. SELECT id, com_string from (SELECT id, GROUP_CONCAT(string SEPARATOR ' ') as com_string FROM table GROUP BY id) temp where com_string like ('%1.0 Inch - 2.9 Inches%') and com_string like ('%Simple Phones%');

Upvotes: 1

Related Questions