CODEWITHSUNDEEP
mysqljoin
gyc
gyc

Reputation: 4360

Mysql returns duplicate entries in simple join

I have 2 tables: course and section as shown in sqlfiddle

I need to select the 'starttime' (or null) for each course's section. Some courses don't have a 'section' entry.

The query:

select section.id, section.course, section.section, IFNULL(schedule.starttime, "null")
from section
join schedule on schedule.courseid = section.course
where course = 3 and section.section > 0

returns has many duplicate entries as there are in the joined table.

id      course  section IFNULL(schedule.starttime, "null")
7       3       1       1440021600
7       3       1       1440021620
7       3       1       1440021640
8       3       2       1440021600
8       3       2       1440021620
8       3       2       1440021640
9       3       3       1440021600
9       3       3       1440021620
9       3       3       1440021640

When I expect:

id      course  section IFNULL(schedule.starttime, "null")
7       3       1       1440021600
8       3       2       1440021620
9       3       3       1440021640
10      3       4       null
11      3       5       null

I tried different combinations of DISTINCT and GROUP BY without success.

Upvotes: 0

Views: 51

Answers (2)

Teve
Teve

Reputation: 56

Try:

SELECT section.id, section.course, section.section, IFNULL(schedule.starttime, "null")
FROM section
LEFT JOIN schedule ON schedule.courseid = section.course AND schedule.id = section.section
WHERE course = 3 AND section.section > 0

Upvotes: 2

Dyrandz Famador
Dyrandz Famador

Reputation: 4525

you can use left join then use the schedule.sectionid and section.id

select section.id, section.course, section.section, IFNULL(schedule.starttime, "")
from section
LEFT JOIN schedule ON schedule.sectionid = section.id
where course = 3 and section.section > 0;

SEE DEMO

Upvotes: 1

Related Questions