Comparing SQL Table

Question

Thanks for looking at this. Someone smarter than me will have this answer but I have reached my limits.

I have three separate tables and am trying to compare them against each other for the purpose of testing some reports. Each of these tables are temporary tables that are populated from separate search queries. They all have an baseID that would link them. example

#1
ID  value
1   5
2   6

#2
ID  value
1   5
2   7

#3
ID  value
1   5
2   7

I have the following query

        SELECT  *   FROM    (SELECT *   FROM    #1  EXCEPT  SELECT  *   FROM    #2) AS  T
UNION ALL   SELECT  *   FROM    (SELECT *   FROM    #2  EXCEPT  SELECT  *   FROM    #1) AS  T
UNION ALL   SELECT  *   FROM    (SELECT *   FROM    #1  EXCEPT  SELECT  *   FROM    #3) AS  T
UNION ALL   SELECT  *   FROM    (SELECT *   FROM    #3  EXCEPT  SELECT  *   FROM    #1) AS  T
UNION ALL   SELECT  *   FROM    (SELECT *   FROM    #2  EXCEPT  SELECT  *   FROM    #3) AS  T
UNION ALL   SELECT  *   FROM    (SELECT *   FROM    #3  EXCEPT  SELECT  *   FROM    #2) AS  T

That works really well to compare the three tables and return any different values between the three. Missing or different data in only one, and such, but if the baseID appears multiple times for the report this fails.

#1
ID  value
1   5
1   6
2   6

#2
ID  value
1   5
1   6
2   7

#3
ID  value
1   5
1   6
2   7

in this set I am getting back a huge amount of results for #1 because the compare is doing both. I can undo this in a group or where clause but I do not know how. Any Ideas?

score 1 · Answer 1 · answered Oct 19 '15 at 18:27

You are still thinking too much in the procedural world. Try to think more of a dataset approach:

SELECT      ID = COALESCE(t1.ID, t2.ID, t3.ID),
            Value = COALESCE(t1.Value, t2.Value, t3.Value)
FROM        #table1 t1
FULL JOIN   #table2 t2 ON t1.ID = t2.ID AND t1.Value = t2.Value
FULL JOIN   #table3 t3 ON (t1.ID = t3.ID AND t1.Value = t3.Value)
                       OR (t2.ID = t3.ID AND t2.Value = t3.Value)
WHERE       t1.ID IS NULL OR t2.ID IS NULL OR t3.ID IS NULL

This is match every record in the 3 table together, giving NULL on non-matches. Since you are only interesting in records that don't exist in the other tables, you filter for NULL only.

trincot · Answer 2 · 2015-10-19T19:07:08.307

If you want to avoid multiplication because of ID having repeated values, try something like this:

SELECT  ID, 
        MIN(V1) AS MINV1,
        MAX(V1) AS MAXV1,
        MIN(V2) AS MINV2,
        MAX(V2) AS MAXV2, 
        MIN(V3) AS MINV3,  
        MAX(V3) AS MAXV3
FROM    (
        SELECT ID, VALUE AS V1, NULL AS V2, NULL AS V3 FROM #1
        UNION
        SELECT ID, NULL, VALUE, NULL FROM #2
        UNION
        SELECT ID, NULL, NULL, VALUE FROM #3
        )
GROUP BY ID
HAVING MIN(V1) <> MAX(v1) 
OR MIN(V1) <> MIN(V2)
OR MIN(V1) <> MAX(V2)
OR MIN(V1) <> MIN(V3)
OR MIN(V1) <> MAX(V3)
OR MIN(V1) IS NULL
OR MAX(V1) IS NULL
OR MIN(V2) IS NULL
OR MAX(V2) IS NULL
OR MIN(V3) IS NULL
OR MAX(V3) IS NULL

This will give at the most one line per ID, and give the two extreme values found in table 1, then the two extremes in table 2, and those in table3... only if not all these values are all the same.

Comparing SQL Table

2 Answers2