CODEWITHSUNDEEP
sqlsql-serversubquerycasewhere-clause
Bodhi
Bodhi

Reputation: 548

Comparing two tables using sub query ?

I can compare two tables for missing data using the below query 

 SELECT S.DEVID,S.INPUTID,S.PRI,S.CCID FROM [DEVICE-SECURITY].DBO.DEVICE_LINK S
  WHERE HID = 4 
  EXCEPT 
  SELECT  T.DEVID,T.INPUTID,T.PRI,T.CCID  FROM  DEVICE-CONFIG.DBO.DEVICE_LINK T
  WHERE HID = 1

The above query returns four rows which are not present in my source table

Now i am trying to use a subquery to achieve the same result 

 SELECT  S.DEVID,S.INPUTID,S.PRI,S.CCID , 
 CASE WHEN S.HID = '4' THEN '1'  END AS HID 
 FROM  [DEVICE-SECURITY].DBO.DEVICE_LINK S
  WHERE S.HID= 4 AND  NOT EXISTS    (
 SELECT T.DEVID,T.INPUTID,T.PRI,T.CCID FROM DEVICE-CONFIG.DBO.DEVICE_LINK  T
   WHERE T.HID = 1 )

It returns no rows .

How to achieve the same result using subquery

Upvotes: 2

Views: 1539

Answers (2)

Jacob Goldhirsch
Jacob Goldhirsch

Reputation: 136

You have two options

You can do a not in filter and then the subquery does not have to connect to the outer

SELECT S.DEVID,S.INPUTID,S.PRI,S.CCID , CASE WHEN S.HID = '4' THEN '1' END AS HID FROM [DEVICE-SECURITY].DBO.DEVICE_LINK S WHERE S.HID= 4 AND  S.DEVID+S.INPUTID+S.PRI+S.CCID NOT in ( SELECT T.DEVID+T.INPUTID+T.PRI+T.CCID FROM DEVICE-CONFIG.DBO.DEVICE_LINK T WHERE T.HID = 1 )

Or you can keep your not exist and connect the subquery to the main by all your fields you want to compare

SELECT S.DEVID,S.INPUTID,S.PRI,S.CCID , CASE WHEN S.HID = '4' THEN '1' END AS HID FROM [DEVICE-SECURITY].DBO.DEVICE_LINK S WHERE S.HID= 4 AND  NOT exists ( SELECT 1 FROM DEVICE-CONFIG.DBO.DEVICE_LINK T WHERE T.HID = 1  and T.DEVID+T.INPUTID+T.PRI+T.CCID=S.DEVID+S.INPUTID+S.PRI+S.CCID)

Upvotes: 1

FuzzyTree
FuzzyTree

Reputation: 32392

An equivalent not exists query is

SELECT S.DEVID,S.INPUTID,S.PRI,S.CCID FROM [DEVICE-SECURITY].DBO.DEVICE_LINK S
  WHERE HID = 4 
  AND NOT EXISTS (
    SELECT 1 FROM  DEVICE-CONFIG.DBO.DEVICE_LINK T
    WHERE HID = 1 
    AND T.DEVID = S.DEVID AND T.INPUTID = S.INPUTID
    AND T.PRI = S.PRI AND T.CCID = S.CCID
)

Upvotes: 1

Related Questions