MySql choose inside another?

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 the identifiers of the elements that the user bought. I want to calculate the sum of all items purchased by the user.

Is the request above legal? If not, what is the correct form?

Answer 1

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

Or maybe you really want to:

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

Answer 2

your query is fine if subselect returns only one row each time.

If more rows are returned, you will need to change your query to:

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

NOTE: in your case, a simple inner join, like the others, would be much more eloquent (and perhaps a little bit faster), but what you wrote is absolutely normal (there are always several ways to get the desired result - and now always it's easy to say that one of them is "best" ;-))

Answer 3

You can also use JOIN syntax

 SELECT sum(price) from table2 t2 join table1 t1 on t1.theID = t2.id WHERE t1.user_id="myid" 

Gotta give you the same result

Answer 4

A JOIN will be more readable:

 SELECT SUM(price) FROM table2 INNER JOIN table1 ON table2.id = table1.theid WHERE table1.user_id = "myid" 

Answer 5

To provide this functionality, you should use SQL JOIN .

 SELECT SUM(table2.price) JOIN table1 ON table2.id=table1.theid WHERE table1.user_id="myid"