CODEWITHSUNDEEP
phpmysqlselect
SReca
SReca

Reputation: 683

Is it possible to use results from a SELECT statement within another SELECT statement?

Here is my code but im sure its not the correct way of doing this.

mysql_query("SELECT * FROM BlockUsers
WHERE OwnerID =
(SELECT ID FROM UserAccounts WHERE Code='$UserCode')
AND
BlockID =
(SELECT ID FROM UserAccounts WHERE Code='$BlockUserCode')
LIMIT 1", $DB)

Can someone help? thanks!

Upvotes: 2

Views: 137

Answers (5)

Tim Gautier
Tim Gautier

Reputation: 30102

SELECT * FROM BlockUsers
WHERE OwnerID IN
(SELECT ID FROM UserAccounts WHERE Code='$UserCode')
AND
BlockID IN
(SELECT ID FROM UserAccounts WHERE Code='$BlockUserCode')
LIMIT 1

or

SELECT * FROM BlockUsers AS bu
INNER JOIN UserAccounts AS ua1 ON ua1.ID = bu.OwnerID
INNER JOIN UserAccounts AS ua2 ON ua2.ID = bu.BlockID
WHERE ua1.Code = '$UserCode' AND ua2.Code = '$BlockUserCode'
LIMIT 1

I think. I didn't test any of this, but I'm pretty sure it's close.

Edit: I just noticed you're using MySQL. You definitely want to do inner joins instead of sub selects. In MySQL those sub selects will create derived tables which have no indexes. Looking for OwnerID and BlockID in those derived tables will do a full table scan of them. This may not matter if $UserCode and $BlockUserCode will narrow the results of the sub selects down to a single row, but if they return quite a few rows it will really slow your query down.

Upvotes: 3

gen_Eric
gen_Eric

Reputation: 227200

Instead of using subqueries, you can just JOIN UserAccounts to get the rows you want.

SELECT BlockUsers.* FROM BlockUsers
JOIN UserAccounts as UA1 ON Code='$UserCode' AND OwnerID = UA1.ID
JOIN UserAccounts as UA2 ON Code='$BlockUserCode' AND BlockID = UA2.ID
LIMIT 1

Upvotes: 2

Andrej
Andrej

Reputation: 7504

Try to use 

SELECT * FROM BlockUsers, UserAccounts
WHERE (OwnerID = ID and BlockID=ID and Code in ('$BlockUserCode', '$UserCode')
LIMIT 1

Upvotes: 0

rroche
rroche

Reputation: 1272

Yes you can, Subqueries and you can find the official mysql reference here: http://dev.mysql.com/doc/refman/5.0/en/subqueries.html and from your query i think you are good to go assuming your subqueries only return 1 value, or as @Marc B points use IN instead of the equal sign.

Upvotes: 1

Marc B
Marc B

Reputation: 360572

Yes, but when you're doing an equality test like that (=, <, >, etc...), the subquery has to return a single value. Otherwise it'd be somevalue = [list of result rows], which makes no sense.

You'd want:

SELECT * FROM BlockUsers
WHERE OwnerID IN (SELECT ID FROM UserAccounts WHERE.....)
              ^^--- use 'in' instead of '=';

Upvotes: 5

Related Questions