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 — Paul Spiegel
– Paul Spiegel, Commented Jan 28, 2018 at 20:54

Paul Spiegel · Accepted Answer · 2018-01-28 21:07:22Z

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, 2018 at 21:07

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Syed Sarmad Ali Over a year ago

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 · Accepted Answer · 2018-01-28 21:12:39Z

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, 2018 at 21:12

answered Jan 28, 2018 at 21:05

Syed Sarmad Ali

231 silver badge9 bronze badges

1 Comment

Radim Bača Over a year ago

the distinct is not needed

Collectives™ on Stack Overflow

Mysql Join returning duplicate values

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related