CODEWITHSUNDEEP
sqloracle-database
Karu3103
Karu3103

Reputation: 81

Merging two table data in Oracle - Column Wise

I have two SQL queries to extract data as per Tables shown below:

====

Query to get table A:

SELECT :DATE AS Date, A.Tracking_ID AS Number, A.Country, COUNT(EmailID) AS EmailSent
FROM
FACT_EMAILS A
LEFT OUTER JOIN DIM_ACCOUNT B
ON A.ACCT_ID=B.ACCT_ID
WHERE A.TIMESTAMP>SYSDATE
AND A.Tracking_ID IN (‘12345’,’23416’,’7890’)
GROUP BY :DATE, A.Tracking_ID, A.Country

Query to get Table B:

SELECT :DATE AS Date, A.Tracking_ID AS Number, A.Country, COUNT(EmailID) AS EmailResponse
FROM
FACT_EMAILS A
LEFT OUTER JOIN DIM_ACCOUNT B
ON A.ACCT_ID=B.ACCT_ID
INNER JOIN DIM_EMAILRESP C
ON C.Email_Type_ID = A.Email_Type_ID
WHERE C.Email_Type_ID IN (1,3,5)
AND A.TIMESTAMP>SYSDATE
AND A.Tracking_ID IN (‘12345’,’23416’,’7890’,)
GROUP BY :DATE, A.Tracking_ID, A.Country

======

Background: There is one extra table that I make use of to get the output I am getting in Table B as I am looking only for records where there is a email response. This table is not being used when I am extracting the data in Table A. Also there is no table A and table B in database. The naming is just used to describe this example.

My question here is how do I combine both Table A, Table B and get output as Table C.

Table A

Table B

Table C (Output)

Can someone please help.

Upvotes: 0

Views: 336

Answers (1)

Caius Jard
Caius Jard

Reputation: 74720

Let's just take your table A and table B data blocks and join them together:

SELECT 
  a.date, 
  a.number, 
  a.country, 
  a.EmailSent, 
  COALESCE(b.EmailResponse,0) as EmailResponse 
FROM
(
 SELECT :DATE AS Date, A.Tracking_ID AS Number, A.Country, COUNT(EmailID) AS 
 EmailSent
 FROM
 FACT_EMAILS A
 LEFT OUTER JOIN DIM_ACCOUNT B
 ON A.ACCT_ID=B.ACCT_ID
 WHERE A.TIMESTAMP>SYSDATE
 AND A.Tracking_ID IN ('12345','23416','7890')
 GROUP BY :DATE, A.Tracking_ID, A.Country
) a

LEFT OUTER JOIN

(    
 SELECT :DATE AS Date, A.Tracking_ID AS Number, A.Country, COUNT(EmailID) AS 
 EmailResponse
 FROM
 FACT_EMAILS A
 LEFT OUTER JOIN DIM_ACCOUNT B
 ON A.ACCT_ID=B.ACCT_ID
 INNER JOIN DIM_EMAILRESP C
 ON C.Email_Type_ID = A.Email_Type_ID
 WHERE C.Email_Type_ID IN (1,3,5)
 AND A.TIMESTAMP>SYSDATE
 AND A.Tracking_ID IN ('12345','23416','7890')
 GROUP BY :DATE, A.Tracking_ID, A.Country

) b
ON
 a.date = b.date AND
 a.Number = b.number AND
 a.country = b.country

Everything inside (...) a is your query that generated A

Everything inside (...) b is your query that generated B

The two are left joined on the common columns. COALESCE is used to put a 0 wherever there is no matching row data in B for EmailsResponse. I've corrected your wonky apostrophes in the IN clause

Realistically this could probably be solved by the much simpler query:

SELECT 
 :DATE AS Date, 
 A.Tracking_ID AS Number,
 A.Country,
 COUNT(A.Tracking_ID) as EmailSent,
 COUNT(C.Email_Type_ID) AS EmailResponse
FROM
 FACT_EMAILS A
 LEFT OUTER JOIN DIM_EMAILRESP C
 ON C.Email_Type_ID = A.Email_Type_ID
 AND C.Email_Type_ID IN (1,3,5)

WHEREA.TIMESTAMP>SYSDATE
 AND A.Tracking_ID IN ('12345','23416','7890')
 GROUP BY :DATE, A.Tracking_ID, A.Country

You didn't post all your table structures/relationships so I've had to guess at some things; like I'm guessing that B was unneeded in the query - it didn't seem to be used

Upvotes: 1

Related Questions