CODEWITHSUNDEEP
mysql
wearetherock
wearetherock

Reputation: 3781

Problem with Query Data in a Table

I have table test contain 4 fields

+----+-----------+--------------+-----+
| id | int_value | string_value | qid |
+----+-----------+--------------+-----+
|  1 |       111 | Red          |   1 |
|  2 |       111 | Green        |   2 |
|  3 |       111 | Blue         |   3 |
|  4 |       222 | Yellow       |   1 |
|  5 |       222 | Red          |   2 |
|  6 |       333 | Red          |   1 |
|  7 |       333 | Green        |   2 |
+----+-----------+--------------+-----+

I want to query int_value that match flowing constraints.

(qid = 1 and string_value = 'Red') and (qid = 2 and string_value = "Green")

The result could be 111 and 333

It's not make sense if I apply this statement

select int_value from test 
where (qid = 1 and string_value = 'Red') 
and (qid = 2 and string_value = "Green")

Does one can help me?
Thank you.

Upvotes: 0

Views: 64

Answers (4)

luiscolorado
luiscolorado

Reputation: 1499

Understanding the problem...

Think about your query. If you take away the parenthesis (because all the conditions are 'and'), it would look like this:

select int_value from test 
where qid = 1 and string_value = 'Red'
and qid = 2 and string_value = "Green"

Furthermore, 'and' has the similar property of the multiplication: the order of the factors doesn't affact the result, so we can do the following:

select int_value from test 
where qid = 1 and qid = 2
 and string_value = "Green" and string_value = 'Red'

As you can see, it's impossible that a single record may a qid equals to 1 and 2; it's just illogical. The same thing for string_value.

Simple answer So, the other solutions that use OR are correct (a qid record my be equal to 1 OR 2). To make your life easy, here is the short answer that also eliminates duplicates:

select distinct int_value from test 
where (qid = 1 and string_value = 'Red') 
or (qid = 2 and string_value = "Green")

Upvotes: 1

Mark Baker
Mark Baker

Reputation: 212442

Possibly not the cleanest method:

select T1.int_value
  from test T1
where exists ( select T2.int_value
                 from test T2
                where T2.int_value = T1.int_value
                  and T2.qid = 1 
                  and T2.string_value = 'Red'
             )
  and exists ( select T3.int_value
                 from test T3
                where T3.int_value = T1.int_value
                  and T3.qid = 2 
                  and T3.string_value = 'Green'
             )

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171451

Rather than constraint, I think you mean condition. You need to use an OR, since there is no one row that can satisfy all WHERE conditions as you have written it:

select int_value 
from test  
where (qid = 1 and string_value = 'Red')  
    or (qid = 2 and string_value = "Green")

If you want the int_values satisfying both conditions, you can do this:

select int_value 
from test  
where (qid = 1 and string_value = 'Red')  
    or (qid = 2 and string_value = "Green")
group by int_value
having count(concat(distinct qid, string_value)) = 2

Upvotes: 4

Matti Virkkunen
Matti Virkkunen

Reputation: 65136

You could use a trick with COUNT:

SELECT int_value
FROM test
WHERE (
    (qid = 1 AND string_value = 'Red')  
    OR (qid = 2 AND string_value = 'Green')
)
GROUP BY int_value
HAVING COUNT(DISTINCT qid, string_value) = 2

This is completely untested, though, so I'm not sure if even the syntax is correct. Basically it groups the results by the int_value, and finds any group where there's as many results as there are different cases. You need to have the numeric value in the HAVING clause match the number of OR alternatives.

Upvotes: 2

Related Questions