Can I use a subquery to complete a where clause?

Question

I have 2 tables joined by the membership.ID and memberDues.memberID. The membership table has a column, firstLast, which is unique for each row. If I have a value for firstLast, i should be able to then find the membership.ID and compare it to the memberDues.memberID. I am using the following query but it doesn't work:

SELECT * FROM memberDues, membership 
WHERE memberID = 
( SELECT ID FROM membership 
WHERE firstLast = 'Jim Smith') 
ORDER BY payDate DESC 
LIMIT 1

The result gets the correct memberDues.memberID and the other data from its row but pulls an unrelated data set from the membership table where even the ID is wrong. What's wrong with the query?

you are doing a cartesian product ... a cross join ... m rows and n rows yields m*n rows in resultset. Then you say, hey, just give me 1 row from the permutation of all — Drew, Nov 03 '15 at 16:18
also, select * with joins tends to deliver some unwelcome columns. Fine for quick queries, but clean that up in production — Drew, Nov 03 '15 at 16:26

score 1 · Accepted Answer · answered Nov 03 '15 at 16:21

1

You can join the two tables on their shared ID values and then you can select only those rows which have firstLast = 'Jim Smith'. I think you can achieve more efficiently what you want (avoiding one additional select) using the following query:

SELECT aa.*, bb.*
FROM memberDues AS aa
INNER JOIN membership AS bb
ON aa.memberID = bb.ID
WHERE bb.firstLast = 'Jim Smith'
ORDER BY aa.payDate DESC
LIMIT 1;

answered Nov 03 '15 at 16:21

Kostas Mitsarakis

4,772
3
23
37

Thanks Kostas, It's an elegant solution and makes sense. It also works! – parboy Nov 03 '15 at 20:54
Great! I am glad that i helped you. – Kostas Mitsarakis Nov 03 '15 at 22:02

score 0 · Answer 2 · answered Nov 03 '15 at 16:18

0

You should use IN instead of equal sign:

SELECT * FROM memberDues, membership WHERE memberID IN
    (SELECT ID FROM membership WHERE firstLast = 'Jim Smith')
    ORDER BY payDate DESC LIMIT 1

answered Nov 03 '15 at 16:18

kulaeff

453
3
12

score 0 · Answer 3 · answered Nov 03 '15 at 16:27

What's wrong with the query?

Your query joins 2 tables without specifying a condition to relate them.

For a fast start on your question, I'd try to specify membership.ID = memberDues.memberID in the where clause.

My personal advice is to use a LEFT JOIN using that condition in the ON clause. But it's more advanced SQL coding, and choices depends on the structure and the needs of this particular application.

Can I use a subquery to complete a where clause?

3 Answers3