John Siniger John Siniger - 4 months ago 12
MySQL Question

Select query result inside WHERE clause

Hello I am trying to make a WHERE clause where the condition is the id of the previous selection, example:

SELECT
,P1.caseid
,(SELECT SUM(P1.amount) FROM table_s P1 WHERE P1.status = 4 AND P1.caseid = 20)
as variable
FROM table_s P1 GROUP BY P1.caseid";


let's say each iteration the
P1.caseid
have value of

20,
45,
20,


How I can insert this value to be the condition of the
WHERE
clause here: WHERE
P1.status = 4 AND P1.caseid = 20


Instead of
P1.caseid
to be = to 20 it have to be equal to the actual
caseid
inside the database for each row.

So for each row it will be:

WHERE P1.caseid = 20
WHERE P1.caseid = 45
WHERE P1.caseid = 35


In this case the number is eqaul to the caseid inside the DB.

TABLE NAME: table_s

id | caseid | amount | status
-- | ------------------------
1 | 20 | 10 | 4
2 | 45 | 10 | 4
3 | 20 | 10 | 4


DB is as follows, the result should be:

1 ROW = caseid: 20 amount: 20 status 4
2 ROW = caseid: 45 amount: 10 status 4


Or

$variable = 20
$variable = 10

Answer

I think I've worked out what you're asking...

The important note here is to use different aliases for your table in the outer and inner queries. Otherwise you have a serious scope problem. (If two instances of the same entity have the same name, how can MySQL ever know which one you're referring to? It will choose the one in the nearest scope. So, instead, call one of them, for example, lookup.)

SELECT 
    P1.*,
    (
     SELECT SUM(lookup.amount)
       FROM table_s   lookup
      WHERE lookup.status = 4
        AND lookup.caseid = P1.caseid
    )
        correlated_sub_query_total_by_caseid
FROM
    table_s P1
GROUP BY
    P1.caseid

That said, you added another comment that seems to contract your question...

the idea is to select the sum of the amount for each caseid and display it. as caseid - sum

For that you just need an aggregation...

SELECT
    caseid,
    SUM(amount)
FROM
    table_s
GROUP BY
    caseid
Comments