select statement

[sploit]

I have a table like this:

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?

[shai1]

Is this what you are after?

select col1 where col2='A' and col2='B';

[fenway]

There are a number of ways to do this; the easiest, IMHO, is the following (UNTESTED):

[code]SELECT col1, COUNT(col2) AS cnt FROM yourTable WHERE col2 IN ('A','B') GROUP BY col1 HAVING cnt = 2[/code]

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.

[wickning1]

[code]SELECT col1, GROUP_CONCAT(col2 SEPARATOR '|') AS con FROM table GROUP BY col1 HAVING con LIKE 'A' AND con LIKE 'B'[/code]
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:
[code]SELECT t1.col1 FROM table t1, table t2 WHERE t1.col1=t2.col1 AND t1.col2='A' AND t2.col2='B'[/code]
You can do a 3-table join for 3 values, etc, but it will be hard to adjust the query dynamically.

[wickning1]

That is brilliant. I gave him 2 other solutions in the PHP Help forum, but that one beats them both. Very nice.

[shoz]

Sploit. It's against the [a href=\"http://www.phpfreaks.com/forums/index.php?act=boardrules\" target=\"_blank\"]Board Guidelines[/a] to post multiple topics on the same issue.

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.