SQL - How to retrieve the result set containing all items of another element?

Question

l'll explain the use-case simply with following scenario. Basically,i want to find all the pts/s from pts_table which contains all the orders of the pl001

    pl_table
    ========
    pl_id | order_id 
    pl001   order001

    pts_table
    =========
    pts_id | order_id
    pts001  order001
    pts002  order001
    pts002  order002

Here is the query im trying,

    SELECT pts_id
    FROM pts_table
    WHERE pts_table.order_id IN (SELECT DISTINCT(plt.order_id) FROM pl_table 
    as plt where plt.pl_id=pl001)// to check element equality.
    GROUP BY pts_id
    HAVING COUNT(DISTINCT pts_table.order_id) = (SELECT COUNT(plt2.order_id) 
    FROM pl_table as plt2 where plt.pl_id=pl001)//to check size equality.

But unfortunately this query returns both pts001 and pts002 which is not correct.it should only return pts001 as the result!. as i figured out this is due to incorrect grouping part.

Can anyone suggest me how to correct this or any other better way? Any help is greatly appreciated.

Just to clarify, it should not return pts002 since pts002 contains `order002` which is not also part of `pl001`? — JNevill, Aug 28 '18 at 17:36

Gordon Linoff · Accepted Answer · 2018-08-28T17:45:34.960

2

This is tricky. It is checking the number of orders that match, not the ones that don't match. Hence, pl002 is not making it into the count.

SELECT p.pts_id
FROM pts_table p LEFT JOIN
     pl_table p2
     ON p.order_id = p2.order_id AND p2.pl_id = 'pl001'
GROUP BY p.pts_id
HAVING COUNT(*) = COUNT(p2.order_id) AND                                    -- All match
       COUNT(*) = (SELECT COUNT(*) FROM pl_table WHERE pl.pl_id = 'pl001')  -- match all of them

edited Aug 28 '18 at 17:45

answered Aug 28 '18 at 17:39

Gordon Linoff

1,242,037
58
646
786

Can you please elaborate more on having conditions you used in this answer? – manu Aug 28 '18 at 17:47
1

@manu . . . The having conditions are saying (1) That all orders in `p` match all orders in `p2` and that the total number of orders in `p` is the same as the total in `p2`. – Gordon Linoff Aug 29 '18 at 02:04
@GordonLinoff , Can you please do some help on the following scenario? just realized if pts_table looks like below, then this above query wont work. `id | pts_id | order_id => 1 pts001 order001` **if pts_id is actually not the pk of that table,(pk is some other auto-increment key like id) then is there any possible modification to fix this?** Because then we get no result (as the two count operations are returning different sums) if we run the query. Any help is greatly appreciated. – Hasanka Sapumal Oct 08 '18 at 12:57

score 0 · Answer 2 · answered Aug 29 '18 at 05:49

Consider the following...

create table pl
(pl_id serial primary key
,order_id int not null
);

insert into pl values 
(1,101);

create table pts
(pts_id int not null
,order_id int not null
,primary key(pts_id,order_id)
);

insert into pts values
(1001,101),
(1002,101),
(1002,102);

SELECT DISTINCT a.*
  FROM pts a
  LEFT
  JOIN
     ( SELECT DISTINCT x.*
         FROM pts x
         LEFT
         JOIN pl y
           ON y.order_id = x.order_id
          AND y.pl_id = 1
        WHERE y.pl_id IS NULL
      ) b
     ON b.pts_id = a.pts_id
  WHERE b.pts_id IS NULL;

Returns 101

http://sqlfiddle.com/#!9/26f411/1

SQL - How to retrieve the result set containing all items of another element?

2 Answers2