B.Sverediuk B.Sverediuk - 2 months ago 8
SQL Question

SQL Server - Compare values from the same table

In SQL Server, I have one table with following data (tblUserSettings):

| CountryID | CityID | UserType | Value1 | Value2 | Value3 |
| 9 | 3 | 1 | 5 | 5 | 5 |
| 9 | 3 | 2 | NULL | NULL | NULL |
| 9 | 3 | 3 | 5 | 5 | 5 |
| 9 | 3 | 4 | 5 | 5 | 5 |
| 9 | 20 | 1 | 5 | 5 | 5 |
| 9 | 20 | 2 | NULL | NULL | NULL |
| 9 | 20 | 3 | 5 | 5 | 5 |
| 9 | 20 | 4 | 0 | 0 | 0 |


I need to compare all the values for all UserTypes from CityID = 20 with all the values for corresponding UserTypes from CityID = 3. The CountryID = 9. The columns to compare are: Value1, Value2, Value3.

I just need to know if all of them are matched to each other or not. I tried to do something as follows:

SELECT CASE WHEN ISNULL(t1.Value1, 0) = ISNULL(t2.Value1, 0) THEN 1 ELSE 0 END AS Match1,
CASE WHEN ISNULL(t1.Value2, 0) = ISNULL(t2.Value2, 0) THEN 1 ELSE 0 END AS Match2,
CASE WHEN ISNULL(t1.Value3, 0) = ISNULL(t2.Value3, 0) THEN 1 ELSE 0 END AS Match3
FROM tblUserSettings t1
INNER JOIN tblUserSettings t2 ON t1.CountryID = t2.CountryID
AND t1.UserType = t2.UserType
AND t1.CityID = 3
AND t2.CityID = 20
WHERE t1.CountryID = 9


And it gives me following result which I have to process further to define if everything matches or not.

| Match1 | Match2 | Match3 |
| 1 | 1 | 1 |
| 1 | 1 | 1 |
| 1 | 1 | 1 |
| 0 | 0 | 0 |


Can I do this in a way to have only one column and row in output - just receive either 1 for all the matches or 0 if at least one doesn't match?

vkp vkp
Answer

If you are looking to get only one column with 1 when all the values match and 0 if atleast one doesn't, use,

SELECT 
CASE WHEN ISNULL(t1.Value1, 0) = ISNULL(t2.Value1, 0) 
      AND ISNULL(t1.Value2, 0) = ISNULL(t2.Value2, 0) 
      AND ISNULL(t1.Value3, 0) = ISNULL(t2.Value3, 0) 
THEN 1 ELSE 0 END AS Match
FROM tblUserSettings t1
INNER JOIN tblUserSettings t2 ON t1.CountryID = t2.CountryID 
           AND t1.UserType = t2.UserType
           AND t1.CityID = 3
           AND t2.CityID = 20
WHERE t1.CountryID = 9