Posted 10 February 2006 - 10:04 PM
col1 | col2
1 | A
1 | B
2 | A
3 | A
3 | B
3 | C
I want to select all col1 where col2 is equal to A and B i.e. the result will be col1=1 and 3
I'm stumped. Can someone help me with the query?
Posted 10 February 2006 - 10:43 PM
SELECT col1, COUNT(col2) AS cnt FROM yourTable WHERE col2 IN ('A','B') GROUP BY col1 HAVING cnt = 2
That is, count the number of answers for each col1 value, query only for A or B, and see where only 2 answers are returned (i.e. A and B).
Hope that helps.
Posted 11 February 2006 - 02:54 PM
SELECT col1, GROUP_CONCAT(col2 SEPARATOR '|') AS con FROM table GROUP BY col1 HAVING con LIKE 'A' AND con LIKE 'B'lol I didn't test that and it'll be slow as hell on a big table (>100000 rows) but it might work for you.
If you know you're always going to be selecting only 2 values for col2 then you can do a join faster:
SELECT t1.col1 FROM table t1, table t2 WHERE t1.col1=t2.col1 AND t1.col2='A' AND t2.col2='B'You can do a 3-table join for 3 values, etc, but it will be hard to adjust the query dynamically.
Posted 11 February 2006 - 04:15 PM
Posted 11 February 2006 - 09:29 PM
I've merged the topics and deleted a few posts that make the thread confusing after the merge. Keep that in mind when reading.
Read through the board guidelines before your next post.
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users