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

Question

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.

Is there a lookup table that contains all possible descriptions? — Giorgos Betsos, Aug 23 '18 at 08:26
You could try distinct on description and assume it as master data — Adinugraha Tawaqal, Aug 23 '18 at 09:09

JohnLBevan · Accepted Answer · 2018-08-23T09:06:12.967

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.

The Second query(where not exists) return C Boat which is incorrect, the first query is working for me — Adinugraha Tawaqal, Aug 23 '18 at 09:02
Thanks @AdinugrahaTawaqal. FYI: The second query should work; only I made a mistake with the demo data (populated `C, Car` twice instead of including `C, Boat`). Tweaking the SQL Fiddle link now... — JohnLBevan, Aug 23 '18 at 09:05

score 2 · Answer 2 · edited Aug 23 '18 at 09:03

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

edited Aug 23 '18 at 09:03

JohnLBevan

22,735
13
96
178

answered Aug 23 '18 at 08:37

DhruvJoshi

17,041
6
41
60

What I try to solve with this is I have master data on multiple database, same structure different contents. I want to make the master data description uniform or at least try to point out differences between database. Each database is it's own company and I work in holding Company. – Adinugraha Tawaqal Aug 23 '18 at 09:05

score 0 · Answer 3 · answered Aug 23 '18 at 08:27

0

use NOT EXISTS

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

answered Aug 23 '18 at 08:27

Squirrel

23,507
4
34
32

There's a slight issue in that this will only return records from `yourtable`; so won't fulfil the task of selecting those records which don't exist in that table as they don't exist. – JohnLBevan Aug 23 '18 at 08:35

score 0 · Answer 4 · answered Aug 23 '18 at 08:57

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

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

4 Answers4