CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Adinugraha Tawaqal
Adinugraha Tawaqal

Reputation: 371

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.

Upvotes: 4

Views: 89

Answers (4)

JohnLBevan
JohnLBevan

Reputation: 24410

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.

Upvotes: 4

DhruvJoshi
DhruvJoshi

Reputation: 17126

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

Upvotes: 2

KumarHarsh
KumarHarsh

Reputation: 5094

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

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

use NOT EXISTS

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

Upvotes: 0

Related Questions