MySql select inside another select?

Question

Is there any way to do this?

SELECT sum(price) from table2 WHERE id=(SELECT theid FROM table1 WHERE user_id="myid")

I have table1 with items' IDs, that a user has purchased. I want to calculate the sum of all items purchased by user.

Is the query above legal? If not, what's the correct form?

Answer 1

Change where id=(SELECT to where id IN (SELECT

Or what you really want is probably:

SELECT sum(price) FROM table2 INNER JOIN table1 ON table2.id = table1.theid WHERE table1.user_id = 'my_id'

Answer 2

you query is ok, as long as the subselect is returning only one row every time.

if there are more rows returned, you'll have to change your query to:

[...] WHERE id IN (SELECT [...]

NOTE: in you case, a simple inner join like others suggested would be much more redable (and maybe a tiny little bit faster) - but what you've written is absolutely ok (there are always multiple ways to get the desired result - and it's now always easy to tell wich one is "the best" ;-) )