Yet another SQL question

Anon

I have a table in my MySQL database which contains two (relevant) columns. the two columns (we'll call them foo and bar) both consist of non-unique integers.

I want to select all distinct values of "foo" which contain each of several values for "bar". For example:

foo bar

12 3
15 3
15 4
17 3

I want to select all of the values of "foo" for which both of the following are true:

-there is a row where bar=3.
-there is a row where bar=4.

in this example, it would only return the value 15.

Is there a way to do this in MySQL < 4.0?

btubalinal

I think you can write the SQL like this:

#Note: I am assuming the table that has foo, bar columns is called 'sample'.

SELECT DISTINCT s1.foo
FROM sample s1
LEFT JOIN sample s2
ON s1.foo = s2.foo
WHERE s1.bar = 3
AND s2.bar = 4;

I hope this helps. It worked when I tried it but I only used your sample data.

Anon

thank you, that was helpful.

I should tell you that my problem was much more complex than this, but the syntax still worked. See, both "foo" and "bar" are actually ids of other tables, and sometimes there are more than two values of bar that I want to check - but I don't want to check these IDs, I want to check usernames which are in another table. In case you are curious, here's the code I eventually came up with to build the query ($includeList is a comma-delimited list of usernames - topicId is "foo" and authorId is "bar", but I actually want the username associated with the authorId):

		$usersArray = explode(",",$includeList);
		$numUsers = sizeof($usersArray);
		$q1 = "SELECT distinct m0.topicId from $cmessages m0 ";
		$q2 = "left join $cusers u0 on (m0.authorId=u0.authorId) ";
		$q3 = "where (u0.username='".$usersArray[0]."'";
		$q4 = ")";
		for($i=1; $i<(sizeof($usersArray)); $i++) {
			$q1 .= ("left join cmessages m".$i." on (m0.topicId=m".$i.".topicId) ");
			$q2 .= ("left join cusers u".$i." on (m".$i.".authorId=u".$i.".authorId) ");
			$q3 .= (" AND u".$i.".username='".$usersArray[$i]."'");
		}
		$topicResult = mysql_query($q1.$q2.$q3.$q4);