Jump to content

select statement


sploit

Recommended Posts

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.