How to select a subject has an empty class

Question

I has 3 Table

Student

id

class_id

Class

class_id

subject_id

class_name

Subject

subject_id

subject_name

How can I select the subject have an empty class ( 1 subject can have many class)

Answer 1

Often, more than one query gives you the same results. Here's one way to do it:

SELECT subject_id, subject_name

FROM subject

WHERE subject_id IN

(SELECT subject_id

FROM Class

WHERE class_id NOT IN

(SELECT DISTINCT class_id

FROM Student));

The innermost subquery gives you all the class_id's in the Student table.

The middle query finds the subject_id of all empty classes, which have no students. The class_id of empty classes will not appear in the list of class_id's returned by the innermost subquery.

The outermost query gets the list of subject_id's with empty classes from the middle query, and gets the subject_name's and subject_id's of those classes.

Answer 2

If I get your question vividly..you are trying to select subject with empty class in sql..you can select by (selecting subject from table1 left join tables2 on tableid.id=tableid.id left join table3 on tableid.id=tableid. id any subject with out a value in it will automatically be assigned null except it was initially assigned not null...I hope this helps.