Jump to content


Photo

select statement


  • Please log in to reply
5 replies to this topic

#1 sploit

sploit
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 10 February 2006 - 10:04 PM

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?



#2 shai1

shai1
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 10 February 2006 - 10:16 PM

Is this what you are after?

select col1 where col2='A' and col2='B';
You always get to choose your own lunch!
http://studio55salononline.com

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 February 2006 - 10:43 PM

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

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

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.

#5 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 11 February 2006 - 04:15 PM

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

#6 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 11 February 2006 - 09:29 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users