How to perform IS_THE_SUBSET_OF function in MySQL?

Question

Added: the CREATE code for student and takes

CREATE TABLE `student` (
    `sid` VARCHAR(6) NOT NULL,
    `sname` VARCHAR(6) NULL DEFAULT NULL,
    `sex` VARCHAR(2) NULL DEFAULT NULL,
    `age` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
    `dept` VARCHAR(50) NULL DEFAULT NULL,
    `class` VARCHAR(4) NULL DEFAULT NULL,
    PRIMARY KEY (`sid`)
);

CREATE TABLE `takes` (
    `sid` VARCHAR(6) NOT NULL,
    `cid` VARCHAR(3) NOT NULL,
    `score` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`sid`, `cid`)
)

I am going to display the IDs and names of all students who have taken the courses which are taken by the student whose ID is '31401'. I have the following code:

SELECT sid, sname
FROM student S
WHERE NOT EXISTS((SELECT cid
                 FROM takes
                 WHERE sid = '31401')
                 EXCEPT
                 (SELECT cid
                 FROM takes T
                 WHERE S.sid = T.sid));

However, there is no EXCEPT operation in MySQL. So I wonder if I could rewrite like this:

SELECT sid, sname
FROM student S
WHERE ((SELECT cid
        FROM takes
        WHERE sid = '31401')
        IS_THE_SUBSET_OF
       (SELECT cid
        FROM takes T
        WHERE S.sid = T.sid));

How can I implement the IS_THE_SUBSET_OF function?

you can use HAVING, IS, IS NOT, IS NOT IN() etc or you could just use joins to do what you want instead save the sub queries and be much quicker. — Dave
– Dave, Commented Apr 16, 2018 at 11:27

Gordon Linoff · Accepted Answer · 2018-04-16 11:27:53Z

1

There is no set-based operator that does what you want. You can use join, group by and some other logic:

select t.sid
from takes t join
     takes ts
     on t.cid = ts.cid and ts.sid = 31401  -- don't use single quotes for a numeric constant
group by t.sid
having count(*) = (select count(*) from takes ts2 where ts2.sid = 31401);

This formulation assumes that takes does not have duplicate sid/cid pairs.

answered Apr 16, 2018 at 11:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to perform IS_THE_SUBSET_OF function in MySQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related