How to do a Select statement inside the main select statement in MySQL
Filed Under (General) by Wenbert on 18-03-2008
Tagged Under : MySQL
A little bit slow but it gets the job done. I used this to generate a report to get the total open_amount for customers.
-
-
SELECT
-
s.parent_no,
-
s.parent_name,
-
t.parent_no,
-
t.col_manager,
-
t.invoice_date,
-
t.due_date,
-
t.over_7_date,
-
t.over_7_by_eom,
-
t.open_amount,
-
-
(SELECT SUM(t2.open_amount) FROM transactions t2
-
WHERE
-
(t2.parent_no=t.parent_no)
-
AND t2.trans_status = ‘open’) AS total
-
-
FROM
-
transactions t
-
LEFT JOIN summary s ON s.parent_no = t.parent_no
-
WHERE
-
( t.col_manager=‘aaa’ OR
-
t.col_manager = ‘bbb’ OR
-
t.col_manager = ‘ccc’ OR
-
t.col_manager = ‘ddd’ OR
-
t.col_manager = ‘eee’ OR
-
t.col_manager = ‘fff’ )
-
AND t.trans_status = ‘open’ ORDER BY s.parent_name ASC
-
