Select rows from two tables in sql

Question

I am having three tables which has primary keys and references. I need to select the rows in below way. Please any one help me.

Table: Class (ClassId -> primarykey)

ClassId  ClassTitle
--------------------
1        First  
2        Second
3        Third

Table: Department(DeptId -> primarykey)

DeptId   DeptName
-------------------
1        science
2        maths
3        general knowledge

Table: ClassAndDepartment

ClassId  DeptId(ClassId-> foreign key of class and DeptId->foreign key of Department)
1        1
1        2
2        1
2        3

Now I need to select the rows where the value exists in the table Class but does not exist in ClassAndDepartment

score 3 · Accepted Answer · answered Oct 19 '12 at 10:33

3

select * from Class c where ClassId not in (select distinct ClassId from ClassAndDepartment)

answered Oct 19 '12 at 10:33

xfx

score 1 · Answer 2 · edited Oct 19 '12 at 10:51

1

select * 
from class c 
where classid not in (select classid from classanddepartment)

edited Oct 19 '12 at 10:51

marc_s

answered Oct 19 '12 at 10:35

Saasu Ganesan

score 0 · Answer 3 · answered May 02 '14 at 15:59

0

An alternative way to solve this is using a LEFT JOIN instead of NOT IN:

SELECT *
FROM Class C
LEFT JOIN ClassAndDepartment CandD ON C.ClassId = CandD.ClassId
WHERE CandD.DeptId IS NULL

Avoiding NOT IN will usually improve performance.

answered May 02 '14 at 15:59

Timothy

3 Answers3