CODEWITHSUNDEEP
sql-server
ondrej
ondrej

Reputation: 997

SQL Server - Select rows where one column matches but the other does not

I need to select rows where data type differs for the same column name.

This is my dataset. Highlighted rows are expected to be in a result.

dataset

I tried following SQL script but it doesn't return expected result

SELECT COLUMN_NAME, DATA_TYPE
FROM tablecolumns
WHERE COLUMN_NAME IN (
    SELECT COLUMN_NAME
    FROM tablecolumns
    WHERE COLUMN_NAME IN (
        SELECT COLUMN_NAME
        FROM tablecolumns
        GROUP BY DATA_TYPE, COLUMN_NAME
        HAVING count(*) = 1)
    GROUP BY COLUMN_NAME
    HAVING count(*) > 1)
ORDER BY COLUMN_NAME

Here is a link to SQL fiddle http://sqlfiddle.com/#!3/6e9665/1

Upvotes: 5

Views: 2503

Answers (2)

Shadi Shaaban
Shadi Shaaban

Reputation: 1720

Firstly, you could get the column names which have more than one data type using GROUP BY and HAVING:

SELECT COLUMN_NAME
FROM tablecolumns
GROUP BY COLUMN_NAME
HAVING count(DISTINCT DATA_TYPE) > 1

Then, you could display all the rows where their columns name lies within that result using WHERE IN(..)

SELECT COLUMN_NAME, DATA_TYPE
FROM tablecolumns
WHERE COLUMN_NAME IN (
   SELECT COLUMN_NAME
   FROM tablecolumns
   Group by COLUMN_NAME
   HAVING count(DISTINCT DATA_TYPE) > 1
)
ORDER BY COLUMN_NAME

Or using INNER JOIN

SELECT a.COLUMN_NAME , a.DATA_TYPE
FROM tablecolumns a
INNER JOIN (
   SELECT b.COLUMN_NAME
   FROM tablecolumns b
   Group by b.COLUMN_NAME
   HAVING count(DISTINCT b.DATA_TYPE) > 1
) c ON c.COLUMN_NAME = a.COLUMN_NAME
ORDER BY a.COLUMN_NAME

Upvotes: 6

Edmond Quinton
Edmond Quinton

Reputation: 1739

If you have SQL Server 2008 or later you can use the COUNT() window function to achieve what you are looking for:

WITH CTE_DistinctColumnsAndTypes
AS
(
    SELECT   COLUMN_NAME
            ,DATA_TYPE
    FROM    tablecolumns
    GROUP BY COLUMN_NAME, DATA_TYPE
),
CTE_ColumnsAndTypeCount
AS
(
    SELECT  COLUMN_NAME
            ,DATA_TYPE
            ,COUNT(DATA_TYPE) OVER (PARTITION BY COLUMN_NAME) AS TypeCount
    FROM CTE_DistinctColumnsAndTypes
)
SELECT  COLUMN_NAME
        ,DATA_TYPE
FROM    CTE_ColumnsAndTypeCount
WHERE   TypeCount > 1

Upvotes: 0

Related Questions