Combine 2 tables into 1 query

Question

I have problem to combining 2 queries into 1 using WITH statement and JOIN. I've tried that but non of it makes the output the way I wanted.

Desired Output

?

The Separate Outputs

JOIN results

Answer 1

WITH your_first_query AS (
  SELECT ...
)
, your_second_query AS (
  SELECT ...
)
SELECT your_first_query.OFFICE
     , your_first_query.MONTH
     , your_first_query.NOMINAL_A
     , your_second_query.NOMINAL_B
FROM   your_first_query
 INNER
  JOIN your_second_query
    ON your_second_query.OFFICE = your_first_query.OFFICE
   AND your_second_query.MONTH = your_first_query.MONTH
ORDER
    BY your_first_query.OFFICE
     , your_first_query.MONTH
;

Answer 2

thank you for all responses sir! i already got an answer

WITH your_first_query AS (
  SELECT ...
)
, your_second_query AS (
  SELECT ...
)
SELECT your_first_query.OFFICE
     , your_first_query.MONTH
     , your_first_query.NOMINAL_A
     , your_second_query.NOMINAL_B
FROM   your_first_query
 FULL
 OUTER
  JOIN your_second_query
    ON your_second_query.OFFICE = your_first_query.OFFICE
   AND your_second_query.MONTH = your_first_query.MONTH
ORDER
    BY your_first_query.OFFICE
     , your_first_query.MONTH
;

Answer 3

So, just looking over your 2 result sets and the desired output you want to Left Outer Join queryresult1 with queryresult2 on their unique keys, Office & Month. A With clause here does not accomplish anything special:

    Select A.Office, A.Month, A.Nominal_A, B.Nominal_B
    From (insert query1 here) A  Left Outer Join
               (Insert query2 here) B on A.Office=B.Office and A.Month=B.Month

I noticed your desired result set was missing everything after SLO office, I assume you got tired of typing and you wanted to show them. If not, you can add a filter to take them out.

Two things that were missing on your original join version,

1. You need the Left Outer Join between the two queries so that the missing offices from query 2 don't cause the data from Query1 to vanish.
2. You need to fully specify the join key as Office=Office AND Month=Month. By only specifying Month, you were telling SQL to give you ALL COMBINATIONS of offices in Query1 with Query2.

Answer 4

Hope that following approach would solve your issue:

WITH NOMINAL_A_CTE (OFFICE, MONTH, NOMINAL_A)
AS
(
SELECT OFFICE, MONTH, NOMINAL_A
FROM NOMINAL_A_QUERY
),
NOMINAL_B_CTE (OFFICE, MONTH, NOMINAL_B)
AS
(
SELECT OFFICE, MONTH, NOMINAL_B
FROM NOMINAL_B_QUERY
)

SELECT A.OFFICE, A.MONTH, SUM(A.NOMINAL_A), SUM(B.NOMINAL_B)
FROM NOMINAL_A_CTE AS A INNER JOIN NOMINAL_B_CTE AS B
ON A.OFFICE = B.OFFICE AND A.MOTNTH = B.MONTH

GROUP BY A.OFFICE, A.MONTH
ORDER BY A.OFFICE, A.MONTH;