CODEWITHSUNDEEP
mysql
Sunil Nepali
Sunil Nepali

Reputation: 62

Retrieve data using joins with multiple conditions

I am trying to write a query which retrieves data from a table which doesn't have data on another table.

I have table A with values

ID  StudentID    CLASSID
1      1            2
2      2            3
3      3            4
4      4            5

TABLE B with values

ID StudentID CLASSID
1   1           2
2   2           3

I am trying to return values from table A with ID 3,4 which is not available in TABLE B.

Query I have tried is

SELECT * FROM A AS a WHERE NOT EXISTS ( SELECT * FROM B AS b WHERE a.student_id = b.student_id AND a.CLASSID = b.CLASSID );

NOTE: As my problem was slow query. I have fixed this problem by creating index which made this query run fast.

Thanks for your effort.

Upvotes: 0

Views: 51

Answers (1)

AnouarZ
AnouarZ

Reputation: 1107

Using LEFT OUTER JOIN

SELECT TableA.* FROM TableA
LEFT OUTER JOIN TableB
ON TableA.ID = TableB.ID
WHERE TableB.ID IS null

Using NOT IN

SELECT * FROM TableA
WHERE TableA.ID NOT IN ( SELECT ID FROM TableB)

Using NO EXISTS

SELECT *
FROM tableA a
WHERE NOT EXISTS
(
    SELECT 1
    FROM tableB b
    WHERE a.studentsID = b.studentsID
    AND a.CLASSID = b.CLASSID
);

Upvotes: 1

Related Questions