CODEWITHSUNDEEP
sql
user3792741
user3792741

Reputation: 23

Combine 2 sql queries into one with eliminating duplicate colums

I have written 2 sql queries by using multiple join statements.

Now I want to combine both the query results as below. First query shows these columns

UDC_ID, EXT_ID, VALUE

The second query shows these columns 

UDC_ID, EXT_ID, VALUE

In both the queries UDC_ID and EXT_ID columns are the same, but the VALUE column in each is different

So the final output I want to display is,

UDC_ID, EXT_ID, VALUE (From Query1), VALUE (from Query 2)

Can anyone suggest how this can be achieved?

These are my queries:

Query 1 joins three tables:

SELECT
  DEV.UDC_ID,
  SR.EXT_ID,
  SRA.VALUE
FROM SERVICE_REQUEST SR
JOIN DEVICE DEV
  ON SR.DEVICE_ID = DEV.ID
JOIN SERVICE_REQUEST_ATTR SRA
  ON SR.ID = SERVICE_REQUEST_ID
WHERE SR.SUB_TYPE_CD = 'HMI_22'
--AND DEV.SUB_TYPE = 'ESME'
AND SRA.NAME = 'CommsHubGUID'
AND SR.INSERT_TIME >= TO_DATE('2016-09-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND SR.INSERT_TIME <= TO_DATE('2016-09-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY SR.INSERT_TIME DESC;

The difference between query 1 and this query is the where clause criterion for SRA.NAME field, otherwise both the queries are same. 

SELECT
  DEV.UDC_ID,
  SR.EXT_ID,
  SRA.VALUE
FROM SERVICE_REQUEST SR
JOIN DEVICE DEV
  ON SR.DEVICE_ID = DEV.ID`enter code here`
JOIN SERVICE_REQUEST_ATTR SRA
  ON SR.ID = SERVICE_REQUEST_ID
WHERE SR.SUB_TYPE_CD = 'HMI_22'
--AND DEV.SUB_TYPE = 'ESME'
AND SRA.NAME = 'Service Location'
AND SR.INSERT_TIME >= TO_DATE('2016-09-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND SR.INSERT_TIME <= TO_DATE('2016-09-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY SR.INSERT_TIME DESC

Upvotes: 1

Views: 38

Answers (1)

StanislavL
StanislavL

Reputation: 57381

SELECT COALESCE(q1.UDC_ID,q2.UDC_ID), 
      COALESCE(q1.EXT_ID, q2.EXT_ID),
      q1.VALUE , 
      q2.VALUE 
FROM (query 1) q1
     FULL OUTER JOIN (query 2) q2 ON q1.UDC_ID=q2.UDC_ID and q1.EXT_ID=q2.EXT_ID

Upvotes: 1

Related Questions