Waxi Waxi - 7 months ago 12
SQL Question

Need help combining SQL queries

SELECT
tableA.col1,
tableA.col2,
LEFT(tableB.col3, 4) as person

FROM tableA

LEFT JOIN tableB ON

tableB.col1 = tableA.col1 AND
tableB.col2 = tableA.col2

WHERE tableA.col3 = '000000'

AND tableA.col4 <> ''

AND person = 'Zeus'

ORDER BY tableA.col1, tableA.col4 ASC;

---

col1 col4 person
001 abc Zeus
002 abc Zeus
003 xyz Zeus
004 xyz Zeus


+

SELECT
tableC.col1,
SUM(tableC.col2) as cost

FROM tableC

WHERE tableC.col3 = 'L'

GROUP BY tableC.col1, tableC.col3;

---

col1 cost
001 23462
002 25215
003 92381
004 29171


=

col1 col4 person cost
001 abc Zeus 23462
002 abc Zeus 25215
003 xyz Zeus 92381
004 xyz Zeus 29171


How do I do this? I tried putting the second query as a nested select in the top one, but I couldn't get it to work. Both result sets share the same
col1
values, which are unique, so I guess they need to be joined on that? And ultimately the
person
is where the query will differ every time I run it.

Answer

You can try with an inner join on col1

SELECT tableA.col1, tableA.col2, LEFT(tableB.col3, 4) as person, tableC.col1, SUM(tableC.col2) as cost
FROM tableA
LEFT JOIN tableB ON ( tableB.col1 = tableA.col1 AND tableB.col2 = tableA.col2)
INNER JOIN tableC ON ( tableA.col1 = tableC.col1)
WHERE tableA.col3 = '000000'
AND tableA.col4 <> ''
AND person = 'Zeus'
GROUP BY tableA.col4, person, tableC.col1, tableC.col3;
ORDER BY tableA.col1, tableA.col4 ASC;
Comments