Find only common column items in a table WITHIN the same group

Question

I have a table that has 3 columns Plate_Id, Prod_id and Location

You can say that the plate_id is the "header" column. The prod_id groups the 'locations' together for a particular 'plate_id'

Given a particular set of values, I only want to pick locations that are 'COMMON' amongst prod_ids for a particular plate_id.

NOTE: My table can have multiple plate_ids

I am close, but its not perfect. I tried to isolate the smallest group for a given plate_id and then tried to inner join it with the original list, but it fails for the scenario where I have 3 prod_ids and a location is common for even one group(i need only locations that are strictly in every prod_id)

Following is the result I desire, based on the how far I have gotten so far,

--     DESIRED RESULT:

--    plate_id    location
--    100            1
--    100            2
--    200            3
--    200            4
--    300            1
--    300            2
--    300            5 


create table #AllTab
(
plate_id int,
prod_id int, 
location int
)

insert into #AllTab
values
(100,10,    1),
(100,10,    2),
(100,10,    3),
(100,10,    4),

(100,20,    1),
(100,20,    2),
(100,20,    3),
(100,20,    4),
(100,20,    5),
(100,20,    6),
(100,20,    9),

(100,30,    1),
(100,30,    2),
(100,30,    9),

(100,40,    1),
(100,40,    2),
(100,40,    12),
(100,40,    14),

(100,40,    1),
(100,40,    2),
(100,40,    25),
(100,40,    30),


-----------------

(200,10,    1),
(200,10,    2),
(200,10,    3),
(200,10,    4),

(200,20,    1),
(200,20,    2),
(200,20,    3),
(200,20,    4),
(200,20,    5),
(200,20,    6),
(200,20,    7),

(200,30,    3),
(200,30,    4),
(200,30,    9),

-----------------

(300,10,    1),
(300,10,    2),
(300,10,    3),
(300,10,    5),

(300,20,    1),
(300,20,    2),
(300,20,    3),
(300,20,    4),
(300,20,    5),
(300,20,    6),
(300,20,    7),
(300,20,    9),

(300,30,    1),
(300,30,    2),
(300,30,    5)


-- The #SubTab table isolates the smallest group from the above table
-- for a particular plate_id

create table #SubTab
(
plate_id int,
prod_id int, 
location int
)

insert into #SubTab
values
(100,30,    1),
(100,30,    2),
(100,30,    9),
------------
(200,30,    3),
(200,30,    4),
(200,30,    9),
------------
(300,30,    1),
(300,30,    2),
(300,30,    5)


select distinct pr.plate_id, pr.prod_id, pr.location from #SubTab pr
inner join #AllTab pl on pr.plate_id = pl.plate_id 
                            and pr.location = pl.location
where pr.Prod_Id <> pl.prod_id
group by  pr.plate_id, pr.prod_id, pr.location

from what I understand the answer you want is answered here: https://stackoverflow.com/a/2594855/9411943 — Jayden, May 22 '18 at 22:04

Gordon Linoff · Answer 1 · 2018-05-23T03:10:43.623

0

This query returns the locations that are in all the products for a given plate:

select plate_id, location
from #alltab a
group by plate_id, location
having count(distinct prod_id) = (select count(distinct prod_id) from #alltab a2 where a2.plate_id = a.plate_id);

This assumes no duplicates in the table -- a reasonable assumption given your data.

Here is a rextester.

edited May 23 '18 at 03:10

answered May 22 '18 at 22:02

Gordon Linoff

1,242,037
58
646
786

I ran your query against the mock table (AllTab) table I posted in my question. It returns 0 columns. Thanks for answering though ! – Anup Amin May 22 '18 at 22:26
@AnupAmin . . . I think there is an error in your data because you have duplicate rows. In any case, the revised version works regardless. – Gordon Linoff May 23 '18 at 03:11

score 0 · Answer 2 · edited Sep 17 '20 at 13:40

I have a solution, it might be a tad lengthy, but works,

SELECT
    SubGroupCounts.plate_id,   
    LocationSubGroupCounts.location
FROM
    (-- Number of sub-grouping relative to main grouping
     SELECT
         plate_id,
         count(distinct prod_id) as num
     FROM
         AllTab
     GROUP BY
         plate_id) SubGroupCounts
INNER JOIN 
    (-- Count the number of sub-groups each location appears in
     SELECT
         plate_id,
         Location, 
         COUNT(distinct prod_id) AS num
     FROM 
         AllTab
     GROUP BY 
         Location, plate_id) LocationSubGroupCounts ON LocationSubGroupCounts.plate_id = SubGroupCounts.plate_id 
                                                    AND LocationSubGroupCounts.num = SubGroupCounts.num

score 0 · Answer 3 · answered May 23 '18 at 06:59

try this:

;with cte1
AS
    (
        Select  Plate_Id,Count(DISTINCT prod_id) as ProdCount
        From #AllTab
        Group by Plate_Id
    )
,cte2
AS
    (
        Select Plate_Id,Location,Count(Location) As LocCount
        from #AllTab
        Group by Plate_Id,Location
    )

SELECT t1.plate_id ,t2.location
FROM cte1 t1 JOIN cte2 t2 
ON t1.Plate_Id =t2.Plate_Id
Where LocCount>=ProdCount

Find only common column items in a table WITHIN the same group

3 Answers3