Jump to content

Archived

This topic is now archived and is closed to further replies.

sploit

select statement

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[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.

Share this post


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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.