CODEWITHSUNDEEP
sqlsql-server
Pål Thingbø
Pål Thingbø

Reputation: 1301

Get parent ID from two tables

I have two tables: @CATS and @NEWCATS

declare @CATS table (_Group int, _Name nvarchar(50))
declare @NEWCATS table (_Name nvarchar(50))

insert into @CATS (_Group, _Name) values (1, 'Siamese'), (1, 'Japanese'), (2, 'Siamese'), (2, 'Japanese'), (2, 'Russian')
insert into @NEWCATS (_Name) values ('Siamese'), ('Japanese')

I want to find if there exists a _Group in @CATS containing exactly the rows from @NEWCATS (e.g. 'Siamese' and 'Japanese').

In this example, I want to return _Group=1, but not _Group=2 (because _Group=2 contains 'Russian').

That is, to complete this:

declare @Group int
select TOP 1 @Group = _Group
from ...

Note: There is no guarantee that the groups are unique, that's why TOP 1.

Upvotes: 0

Views: 41

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You can use EXCEPT:

SELECT DISTINCT _group
FROM @CATS
EXCEPT
SELECT c._group
FROM @CATS c
LEFT JOIN @NEWCATS nc
  ON nc._Name = c._Name
WHERE nc._Name IS NULL;

LiveDemo

EDIT:

WITH cte AS 
(
  SELECT *
  FROM (SELECT DISTINCT _Group FROM @CATS) AS c
  CROSS JOIN @NEWCATS nc
)
SELECT DISTINCT _group
FROM @CATS
EXCEPT
SELECT COALESCE(c._Group, cte._Group) AS _Group
FROM @CATS c
FULL JOIN cte
  ON c._Group = cte._Group
 AND c._Name = cte._Name
WHERE c._Name IS NULL
   OR cte._Name IS NULL;

LiveDemo2

Upvotes: 1

trincot
trincot

Reputation: 350137

This is a way to require an exact match, so that a found group has exactly all, but no other names as present in the @NEWCATS table:

SELECT    @CATS._Group
FROM      @CATS
LEFT JOIN @NEWCATS ON @CATS._Name = @NEWCATS._Name
GROUP BY  @CATS._Group
HAVING    COUNT(DISTINCT @NEWCATS._Name)
        = (SELECT COUNT(DISTINCT _Name) FROM @NEWCATS);

Upvotes: 0

Andreas
Andreas

Reputation: 159086

This is one way to do it:

select CATS._Group
  from CATS
  left join NEWCATS on NEWCATS._Name = CATS._Name
 group by CATS._Group
having sum(case when NEWCATS._Name is null then 1 else 0 end) = 0

If they were real tables and there were indexes, other ways using different access plans could produce faster results.

Update

Given new requirement that all rows in NEWCATS must be referenced:

select CATS._Group
  from CATS
  left join NEWCATS on NEWCATS._Name = CATS._Name
 group by CATS._Group
having sum(case when NEWCATS._Name is null then 1 else 0 end) = 0
   and count(*) = ( select count(*) from NEWCATS )

Upvotes: 2

Related Questions