Joseph Kraemer Joseph Kraemer - 2 months ago 8
MySQL Question

How do i sub query two joins?

I have created two queries that both return the required results independent of each other. I am trying to join them to have the returned values be customerName, Amount Ordered, and Amount Paid.

Currently, this query works but only returns the customerName. How can it get the query to return the other two columns?

SELECT c1.customerName
FROM
(SELECT cc.customerName, ROUND(SUM(od.priceEach * od.quantityOrdered), 2) as '$ Amount Ordered'
FROM customers cc
INNER JOIN orders o ON o.customerNumber = cc.customerNumber
INNER JOIN orderdetails od ON od.orderNumber = o.orderNumber
GROUP BY cc.customerName
) c1
INNER JOIN
(SELECT c.customerName, ROUND(SUM(p.amount), 2) as 'Total $ Amount Paid'
FROM customers c
INNER JOIN payments p ON p.customerNumber = c.customerNumber
GROUP BY c.customerName
) c2
WHERE c1.customerName = c2.customerName
GROUP BY c1.customerName
ORDER BY c1.customerName;

Answer

this should select the others column

SELECT c1.customerName, c1.Amount_Ordered as '$ Amount Ordered', c2.Total_Amount_Paid  as 'Total $ Amount Paid'
FROM 
    (SELECT cc.customerName, ROUND(SUM(od.priceEach * od.quantityOrdered), 2) as Amount_Ordered
     FROM customers cc
     INNER JOIN orders o ON o.customerNumber = cc.customerNumber
     INNER JOIN orderdetails od ON od.orderNumber = o.orderNumber
     GROUP BY cc.customerName
    ) c1
 INNER JOIN
    (SELECT c.customerName, ROUND(SUM(p.amount), 2) as  Total_Amount_Paid
     FROM customers c
     INNER JOIN payments p ON p.customerNumber = c.customerNumber
     GROUP BY c.customerName
    ) c2
WHERE c1.customerName = c2.customerName
GROUP BY c1.customerName
ORDER BY c1.customerName;