Techworld Techworld - 6 months ago 9
SQL Question

SQL Query to show opposite values

I need help in this query. Suppose there is the following table

| Node_Name| Status |
+----------+-----------+
| Node_1 | a |
| Node_1 | b |
| Node_2 | c |
| Node_2 | a |
| Node_3 | b |
| Node_3 | c |


I need to get all the statuses that a node does not have.

For example output should be as follows:

| Node_Name| Status |
+----------+-----------+
| Node_1 | c |
| Node_2 | b |
| Node_3 | a |


Any help would be great! thanks

Answer

First find all the possible combination of node_name and status

Then Left Outer join the above result with yourtable and filter only the non matching records to get the result

Try this

SELECT A.node_name, 
       B.status 
FROM   (SELECT DISTINCT node_name 
        FROM   yourtable) A 
       CROSS JOIN (SELECT DISTINCT status 
                   FROM   yourtable) B 
       LEFT OUTER JOIN yourtable C 
                    ON A.node_name = C.node_name 
                       AND C.status = B.status 
WHERE  C.status IS NULL