Vfrdtyky

(SQL rookie question) I'm Working on a query which could find all the Worker who create products that include all the products created by Worker with id = 18.

CREATE TABLE Creates(

        ID CHAR(10),

        PRODUCTID CHAR(10),

        PRIMARY KEY (ID, PRODUCTID),

        FOREIGN KEY(ID) REFERENCES Worker(ID) ON DELETE CASCADE,

    };



CREATE TABLE Worker(

        ID CHAR(10) PRIMARY KEY,

    };

Here are what have in my table Creates now:

INSERT INTO Creates VALUES('2', 'S100');

INSERT INTO Creates VALUES('2', 'D111');

INSERT INTO Creates VALUES('4', 'D111');

INSERT INTO Creates VALUES('4', 'S119');

INSERT INTO Creates VALUES('6', 'S2');

INSERT INTO Creates VALUES('6', 'D231');

INSERT INTO Creates VALUES('8', 'S103');

INSERT INTO Creates VALUES('10', 'S109');

INSERT INTO Creates VALUES('12', 'S189');

INSERT INTO Creates VALUES('14', 'S982');

INSERT INTO Creates VALUES('20', 'E341');

INSERT INTO Creates VALUES('22', 'E100');

INSERT INTO Creates VALUES('18', 'D111');

INSERT INTO Creates VALUES('18', 'D231');

INSERT INTO Creates VALUES('2', 'D231');

INSERT INTO Creates VALUES('24', 'D111');

INSERT INTO Creates VALUES('24', 'D231');

As we can see, actually the correct output should be ID 2 and 24.

This what I have now to make it true, but I don't think this correct, so I would like to hear from anyone or any suggestion that can help me with it, thank you in advance.

SELECT DISTINCT ID 

    FROM Creates 

    WHERE PRODUCTID = ALL (SELECT PRODUCTID FROM Creates WHERE ID = '18')

    ORDER BY ID ASC

；

This:

SELECT ID 

FROM 

    (SELECT * 

     FROM Creates 

     WHERE PRODUCTID IN (SELECT PRODUCTID FROM Creates WHERE ID = '18') 

       AND (ID <> '18')

    ) AS t

GROUP BY ID

HAVING COUNT(*) = (SELECT COUNT(*) FROM Creates WHERE ID = '18')

returns

2

24

As you can see, the Worker table is not involved at all.

The results are retrieved from Creates table by selecting the ids of the workers that have produced products that the worker with id '18' only if their number is equal to the number of the products that the worker with id '18' has produced.

First you need a list of all products created by worker 18

SELECT product_id FROM Creates WHERE ID = '18'

Then you need a list of all employees

SELECT ID as worker_id FROM Workers WHERE ID <> '18'

Now you create a cartesian product of both results to have a list of all possible combinations

WITH products as (

  SELECT product_id FROM Creates WHERE ID = '18'

), employees as (

  SELECT ID as worker_id FROM Workers WHERE ID <> '18'

)

SELECT product_id, worker_id

FROM products

CROSS JOIN employees

Now you have to find if an employees have every product so you check with the Creates Table

WITH products as (

  SELECT product_id FROM Creates WHERE ID = '18'

), employees as (

  SELECT ID as worker_id FROM Workers WHERE ID <> '18'

), allMatches as (

  SELECT product_id, worker_id

  FROM products

  CROSS JOIN employees

)

SELECT A.worker_id

FROM allMatches A

LEFT JOIN Creates C

  ON A.product_id = C.product_id

 AND A.worker_id = C.worker_id

GROUP BY A.worker_id

HAVING COUNT(*) = COUNT(C.product_id)

if a worker doesnt create a product the LEFT JOIN will create NULL on the match. And COUNT(C.product_id) doesnt count nulls

Probably the simplest method uses listagg():

select c.id,

       listagg(c.productid, ',') within group (order by c.productid) as products

from creates c join

     creates c18

     on c18.productid = c.productid and

        c18.id = 18 and

        c.id <> 18 

group by c.id

having products = (select listagg(c.productid, ',') within group (order by c.productid) as products

                   from creates c

                   where c.id = 18

                  );