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'

Copy Code

References

SQL Server Select
SQL Server From
SQL Server Inner Join
SQL Server Where
SQL Server Sum
SQL Server Group By