Mysql Join returning duplicate values

Question

i have two tables timetable and subject and i need id,subject_name from subject table which is not used in timetable for specific section.

timetable table subject table

but it is returning multiple values as you can see in image below.

I am using this query

SELECT subject.id, subject.subject_name 
FROM timetable 
LEFT JOIN subject 
ON timetable.subject_id != subject.id AND timetable.class_id = subject.class_id 
WHERE timetable.class_id = 1 and timetable.section_id =1

Possible duplicate of [SQL - find records from one table which don't exist in another](https://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) — Paul Spiegel, Jan 28 '18 at 20:54

score 1 · Answer 1 · answered Jan 28 '18 at 21:07

1

I still think it's a duplicate question. Though your issue is a bit more complex. So try this one:

SELECT subject.id, subject.subject_name 
FROM subject
LEFT JOIN timetable 
  ON  timetable.subject_id = subject.id
  AND timetable.class_id   = subject.class_id 
  AND timetable.section_id = 1
WHERE subject.class_id = 1
  AND timetable.id IS NULL

answered Jan 28 '18 at 21:07

Paul Spiegel

30,925
5
44
53

This worked... i also made a query which also works SELECT id, subject_name FROM subject WHERE class_id = 1 AND id NOT IN (SELECT subject_id from timetable where section_id=1) – Syed Sarmad Ali Jan 28 '18 at 21:13

Syed Sarmad Ali · Accepted Answer · 2018-01-28T21:12:39.897

0

This simple query worked.

SELECT id, subject_name FROM subject WHERE class_id = 1 
AND id NOT IN (SELECT subject_id from timetable where section_id=1)

edited Jan 28 '18 at 21:12

answered Jan 28 '18 at 21:05

Syed Sarmad Ali

23
1
8

1

the `distinct` is not needed – Radim Bača Jan 28 '18 at 21:06

Mysql Join returning duplicate values

2 Answers2