How to use a subquery in a SELECT statement (SQL Server/MySQL)
Often I need to produce a query over a parent table and get a total of values from a child table. This can be accomplished with a single query that utilizes a subquery within the SELECT statement. In the following example, we’re selecting parent values from a table named tbl_loans and utilizing a subquery to select the total of the amount paid toward principal from a child table named tbl_loanpayments. For each row in the table tbl_loans, there could be multiple child rows in the table tbl_loanpayments. We want the amount paid to the principal for all loan payment rows that have an amount paid not equal zero.
The following example will work in SQL Server or MySQL.
SELECT tbl_loans.loan_id , tbl_loans.loan_amount , tbl_loans.loan_rate , tbl_loans.loan_term , tbl_members.member_name , ( SELECT SUM(loanpayment_principal) FROM tbl_loanpayments WHERE loanpayment_loan = loan_id AND loanpayment_paid <> 0 GROUP BY loanpayment_loan ) AS amount_paid FROM tbl_loans INNER JOIN tbl_members ON tbl_loans.loan_member = tbl_members.member_id WHERE loan_status='A'