CODEWITHSUNDEEP
mysqlsubquery
Michał Graczyk
Michał Graczyk

Reputation: 63

How to use data from WHERE query MYSQL

I have this MySQL query:

SELECT * FROM A

LEFT JOIN B
ON B.id = A.id

LEFT JOIN C
ON C.other = A.other

WHERE

(SELECT SUM(C.quantity) FROM C WHERE C.other = A.other) < A.quantity

Is any way to get values from SELECT from WHERE clause? For example for PHP to use? Something like:

WHERE

(SELECT SUM(C.quantity) FROM C WHERE C.other = A.other) AS quantity < A.quantity

I need to compare this two values in mysql and then i need them both in my script. I can of course put this subquery into first SELECT - but i belive there is better way to do this than repeat code in many places.

Upvotes: 0

Views: 46

Answers (1)

trincot
trincot

Reputation: 350147

You could do that as follows:

SELECT    A.*, B.*, C.*, D.sumq 
FROM      A
LEFT JOIN B
       ON B.id = A.id
LEFT JOIN C
       ON C.other = A.other
LEFT JOIN (
           SELECT   other, SUM(quantity) sumq
           FROM     C
           GROUP BY other
          ) AS D
       ON D.other = A.other
WHERE     COALESCE(D.sumq, 0) < A.quantity

Upvotes: 1

Related Questions