SQL query to select students who have taken all subjects from subjects table

53
January 26, 2021, at 12:40 PM

I have two tables, student_records and subjects. I have to find out those students who have taken all the subjects (1,2,3) from the subjects table.

create table subject (ID int primary key, subject varchar(50));
insert into subject values (1,'English'),(2,'Bengali'),(3,'Math');
create table student_records(ID int primary key,student_id int , sub_id int,foreign key (sub_id) 
references subject(ID));
insert into student_records values (1,1,1),(2,3,2),(3,1,3),(4,1,2),(5,2,1),(6,3,3),(7,3,1),(8,4,1);
/*select *from student_records;
select *from subject;*/

I have done with this

select student_id from student_records
    group by student_id
    having count(*)=(select count(*) from subject);

Is there any better query?

Answer 1

Not a "better" way, but I would make sure to use count distinct in this case:

SELECT
    student_id
    ,COUNT( distinct  sub_id) AS subject_cnt
FROM student_records
GROUP BY student_id
HAVING subject_cnt = SELECT COUNT(*) FROM subject
READ ALSO
Sticky view with scrollview and expandable background

Sticky view with scrollview and expandable background

How can we make a view in red border sticky which does not depend on the height of black background and always stick with a border of black and white background

93
div element hides when load() function is called

div element hides when load() function is called

I'm working on making ordering by some criterias(eg

63
Displaying Dynamic placeholder content on Textbox in ReactJS

Displaying Dynamic placeholder content on Textbox in ReactJS

I wanted to place a dynamic content in placeholder inside my textbox component in ReactI have declared a function which returns the placeholder, passing the value as argument

83
Extracting Parameters from Redirect URI - QuickBooks API / Python

Extracting Parameters from Redirect URI - QuickBooks API / Python

I'm working on the authentication component of an app that calls the quickbooks online reporting APIIntuit has an authentication package, intuit-oauth, which I'm using to generate the authorization URL

87