CODEWITHSUNDEEP
sqlpostgresql
BrDaHa
BrDaHa

Reputation: 5770

Comparing sets of values in same table

I'm trying to compare two sets of data in the same table to verify an update operation. I've created this query to view the different sets of information side-by-side, but when I add an additional constraint in the WHERE clause, I get zero rows returned.

The following query shows me the two record sets next to each other, so I can kinda eyeball that there are different componentids:

WITH src AS 
     (SELECT id AS s_id,
             moduleid AS s_moduleid, 
             instanceid AS s_instanceid, 
             tagid AS s_tagid, 
             componentid AS s_componentid 
      FROM component_junction WHERE id=103)
SELECT * FROM component_junction cj 
JOIN src ON s_moduleid=cj.moduleid 
         AND s_instanceid=cj.instanceid 
         AND s_tagid=cj.tagid 
WHERE cj.id=117

Returns:

id  | moduleid | instanceid | tagid | componentid | s_id | s_moduleid | s_instanceid | s_tagid | s_componentid
----|----------|------------|-------|-------------|------|------------|--------------|---------|--------------
117 | 2923     | 7179       | 1     |   <null>    | 103  | 2923       | 7179         | 1       | <null>
117 | 2923     | 7179       | 2     |   <null>    | 103  | 2923       | 7179         | 2       | <null>
117 | 2924     | 1404       | 1     |   <null>    | 103  | 2924       | 1404         | 1       | <null>
117 | 2924     | 1404       | 2     |   <null>    | 103  | 2924       | 1404         | 2       | <null>
117 | 1        | 41         | 2     |   <null>    | 103  | 1          | 41           | 2       | 267
117 | 1        | 40         | 2     |   <null>    | 103  | 1          | 40           | 2       | 267
117 | 1        | 38         | 2     |   <null>    | 103  | 1          | 38           | 2       | 267

But the below query does not return me any rows. Note the extra AND clause at the end:

WITH src AS 
     (SELECT id AS s_id,
             moduleid AS s_moduleid, 
             instanceid AS s_instanceid, 
             tagid AS s_tagid, 
             componentid AS s_componentid 
      FROM component_junction WHERE id=103)
SELECT * FROM component_junction cj 
JOIN src ON s_moduleid=cj.moduleid 
         AND s_instanceid=cj.instanceid 
         AND s_tagid=cj.tagid 
WHERE cj.id=117 AND s_componentid != cj.componentid;

I know the values are different since I can see it in the result set from the first query. Some NULL values are present in both sets for componentid so I'd expect those not to show in the second query.

Upvotes: 0

Views: 601

Answers (2)

Murali Dhar Darshan
Murali Dhar Darshan

Reputation: 295

As one NULL value differs from another NULL value, you may use ISNULL function for ComponentID column like below:

 WITH src AS 
 (SELECT id AS s_id,
         moduleid AS s_moduleid, 
         instanceid AS s_instanceid, 
         tagid AS s_tagid, 
          ISNULL(componentid,'') AS s_componentid 
  FROM component_junction WHERE id=103)
  SELECT * FROM component_junction cj 
  JOIN src ON s_moduleid=cj.moduleid 
     AND s_instanceid=cj.instanceid 
     AND s_tagid=cj.tagid 
     WHERE cj.id=117 
     AND ISNULL(s_componentid,'') != ISNULL(cj.componentid,'');

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

One or both values appear to be NULL. Postgres supports the ANSI standard NULL safe comparison, so change

s_componentid != cj.componentid

to:

s_componentid is distinct from cj.componentid

Upvotes: 4

Related Questions