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

user_8181663 0
Viewed 70

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.

having group-by mysql sql
2 Answers

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
user_11440350

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

user_15296730
Related