CODEWITHSUNDEEP
mysqlselect
XCS
XCS

Reputation: 28137

MySql select inside another select?

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?

Upvotes: 9

Views: 39571

Answers (5)

Matt
Matt

Reputation: 562

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'

Upvotes: 19

oezi
oezi

Reputation: 51797

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" ;-) )

Upvotes: 7

ahmet alp balkan
ahmet alp balkan

Reputation: 45206

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"

Upvotes: 1

Sparky
Sparky

Reputation: 15085

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

Upvotes: 2

Phil Hunt
Phil Hunt

Reputation: 8521

A JOIN would be more readable:

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

Upvotes: 1

Related Questions