Basic SQL joining two select statements-CodePudding

I have two select statements that work just fine separately, but I don't know how to put them together into one resulting table. I've been trying SELECT INTO or creating temporary tables, but I am just not getting anywhere. Hoping that if I post the two select statements below, it'll take someone 30 seconds to point me in the right direction.

SELECT EMEQP#, EMLYR$, SUM(EQCRRV) AS SUM_REV
FROM WSDATA.EQPMASFL
LEFT JOIN WSDATA.CREQURFL ON EMCMP = EQCMP AND EMEQP# = EQEQP#
WHERE EMEQP# IN ('10925592','10935431','11192212') AND EQPERD >=202101 AND EQPERD <=202112
GROUP BY EMEQP#, EMLYR$

SELECT EMEQP#, EMLYRM AS PARTS, EMLYRL AS LABOR, EMLYRM   EMLYRL AS PARTS_LABOR, SUM(RHAMT$) AS R_M
FROM WSDATA.EQPMASFL 
LEFT JOIN WSDATA.WOHEADFL ON EMCMP = VHCMP AND EMEQP# = VHEQP#
LEFT JOIN WSDATA.RACHDRFL ON RHCMP = VHCMP AND RHCON# = VHWO#
WHERE EMEQP# IN ('10925592','10935431','11192212') AND VHDATC >= '20210101' AND VHDATC <= '20211231' AND VHBCOL = 'L'
GROUP BY EMEQP#, EMLYRM, EMLYRL, EMLYRM   EMLYRL

CodePudding user response：

two major pieces of information is missing.

what is the primary key of the table?
What kind of database is this?

It's a bit confusing on what the tables look like, but I'm going to assume that EMEQP# is a primary key in the EQPMASFL table.

if that's the case, you can perform a join using a couple of CTEs. if that's not the case, you have to add a field to each query that can be joined when the queries output.

you can do this:

with q1 as (
     SELECT EMEQP#, EMLYR$, SUM(EQCRRV) AS SUM_REV
     FROM WSDATA.EQPMASFL
     LEFT JOIN WSDATA.CREQURFL ON EMCMP = EQCMP AND EMEQP# = EQEQP#
     WHERE EMEQP# IN ('10925592','10935431','11192212') AND EQPERD >=202101 AND 
     EQPERD <=202112
     GROUP BY EMEQP#, EMLYR$
), q2 as (
     SELECT EMEQP#, EMLYRM AS PARTS, EMLYRL AS LABOR, EMLYRM   EMLYRL AS 
     PARTS_LABOR, SUM(RHAMT$) AS R_M
     FROM WSDATA.EQPMASFL 
     LEFT JOIN WSDATA.WOHEADFL ON EMCMP = VHCMP AND EMEQP# = VHEQP#
     LEFT JOIN WSDATA.RACHDRFL ON RHCMP = VHCMP AND RHCON# = VHWO#
     WHERE EMEQP# IN ('10925592','10935431','11192212') AND VHDATC >= 
     '20210101' AND VHDATC <= '20211231' AND VHBCOL = 'L'
     GROUP BY EMEQP#, EMLYRM, EMLYRL, EMLYRM   EMLYRL
) select * from q1
full outer join q2 on q1.EMEQP# = q2.EMEQP#

The reason why I"m doing this in this manner is due to the different filtering criteria in each query. with the cte's (see this article), you can use the output as data sources for follow on queries.

This comes at a cost, if you do a large query that references the cte, the cte requires each time. so use them sparingly.