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?
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'
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" ;-) )
You can also use the JOIN syntax
SELECT sum(price) from table2 t2
join table1 t1 on t1.theID = t2.id
WHERE t1.user_id="myid"
Should give you the same result
A JOIN would be more readable:
SELECT SUM(price) FROM table2
INNER JOIN table1 ON table2.id = table1.theid
WHERE table1.user_id = "myid"
You should use SQL JOIN to provide that functionality.
SELECT SUM(table2.price) JOIN table1 ON
table2.id=table1.theid WHERE table1.user_id="myid"
