A web developer's blog. PHP, MySQL, CakePHP, Zend Framework, Wordpress, Code Igniter, Django, Python, CSS, Javascript, jQuery, Knockout.js, and other web development topics.

How to do a Select statement inside the main select statement in 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
This entry was posted in General and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>