'Select item that is different in each of group by a column

I have this sample table

+--------+-------------+
| DBName | Description |
+--------+-------------+
| A      | Car         |
| A      | Boat        |
| B      | Car         |
| B      | Plane       |
| C      | Car         |
| C      | Boat        |
| C      | Plane       |
+--------+-------------+

I want to take only Description that is not exist on every DBName and show what DBName that don't have the Description.

The Result from the query that I want

+--------+-------------+
| DBName | Description |
+--------+-------------+
| A      | Plane       |
| B      | Boat        |
+--------+-------------+

Keep in mind it will be more than just A,B,C on DBName.



Solution 1:[1]

Interesting issue. Here are a couple of options for solving. There's discussions around these techniques here, along with a few suggestions of other routes for handling scenarios such as this.


SQL Fiddle Example

select DBName, Description
from (
    select DBName, Description
    from (select distinct DBName from demo) a
    cross join (select distinct Description from demo) b
) c
except
select DbName, Description from demo

This solution works by fetching every possible combination (via cross join of distinct values for each column), then excluding all those which already exist via the except clause.


SQL Fiddle Example

select [each].DBName, missing.Description
from (select distinct DBName from demo) [each]
cross join (select distinct Description from demo) [missing]
where not exists 
(
  select top 1 1
  from demo [exists]
  where [exists].DbName = [each].DBName
  and [exists].Description = [missing].Description
)

This solution is the same as the above, only instead of the except cluase we use where not exists to remove existing combos.

Solution 2:[2]

Ideally you should have a master list of data. In case you do not you should deriv3 it from the data and then put checks against them like below:

SQL Fiddle Example

select 
masterlistDbname.Dbname,
masterlistDesc.Description 
from
(
    select distinct Description from yourtable
) masterlistDesc
cross join
(
    select distinct Dbname from yourtable
) masterlistDbname
left join
yourtable t1 
on t1.Dbname = masterlistDbname.Dbname
and t1.Description = masterlistDesc.Description 
where t1.Dbname is NULL

Solution 3:[3]

use NOT EXISTS

SELECT *
FROM   yourtable t
WHERE  NOT EXISTS
       (
           SELECT *
           FROM   yourtable x
           WHERE  x.Description = t.Description 
           AND    x.DBName     <> t.DBName 
       ) 

Solution 4:[4]

you should throw little more Sample data.

Try this,

create table #test(DBName varchar(50),Descriptions varchar(50) )

insert into #test VALUES
('A','Car')        
,('A','Boat')       
,('B','Car')        
,('B','Plane')      
,('C','Car')        
,('C','Boat')       
,('C','Plane')      

;

WITH CTE
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            ORDER BY (
                    SELECT NULL
                    )
            ) rn
        ,ROW_NUMBER() OVER (
            PARTITION BY DBName ORDER BY (
                    SELECT NULL
                    )
            ) rn1
    FROM #test
    )
SELECT t.DBName
    ,t1.Descriptions
FROM cte t
CROSS APPLY (
    SELECT TOP 1 Descriptions
    FROM cte t1
    WHERE t1.rn > t.rn
        AND t.Descriptions <> t1.Descriptions
        AND t.dbname <> t1.dbname
    ORDER BY t1.rn
    ) t1
WHERE t.rn1 = 1



drop table #test

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 JohnLBevan
Solution 3 Squirrel
Solution 4 KumarHarsh