left join table with two conditions

Question

I need to left join between two tables to take courses in the first table, and non-existent in the second table. But I also want to take courses that mark smaller than 60 in the second table(Even if it was exist in two tables).

SELECT   offerd_course.id,offerd_course.course_id,
         offerd_course.instructor_name,offerd_course.hour, 
         offerd_course.course_name, 
         offerd_course.day,offerd_course.place,
         offerd_course.type
FROM offerd_course
LEFT JOIN student_course
ON offerd_course.course_id = student_course.course_id 
WHERE  student_course.mark >=60 AND student_course.course_id IS NULL

How can do it ?

Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). — Jay Blanchard, Nov 14 '14 at 16:06

score 1 · Answer 1 · answered Nov 14 '14 at 16:10

1

$query = mysql_query("SELECT offerd_course.id,offerd_course.course_id,offerd_course.instructor_name,offerd_course.hour, offerd_course.course_name, offerd_course.day,offerd_course.place,offerd_course.type
    FROM offerd_course
    LEFT JOIN student_course
    ON offerd_course.course_id = student_course.course_id
    AND student_course.mark >=60
    AND student_course.course_id is NULL

") or die(mysql_error()) ;

answered Nov 14 '14 at 16:10

RichardBernards

3,146
1
22
30

1

+1 Though it would help to also explain why moving the criteria to the join works. – xQbert Nov 14 '14 at 16:12

score 1 · Accepted Answer · answered Nov 14 '14 at 16:29

Try This:

SELECT   offerd_course.id,offerd_course.course_id,
         offerd_course.instructor_name,offerd_course.hour, 
         offerd_course.course_name, 
         offerd_course.day,offerd_course.place,
         offerd_course.type
FROM offerd_course
LEFT JOIN student_course
ON offerd_course.course_id = student_course.course_id 
WHERE  student_course.mark >=60 AND student_course.course_id IS NULL
UNION
SELECT   offerd_course.id,offerd_course.course_id,
         offerd_course.instructor_name,offerd_course.hour, 
         offerd_course.course_name, 
         offerd_course.day,offerd_course.place,
         offerd_course.type
FROM offerd_course
 JOIN student_course
ON offerd_course.course_id = student_course.course_id 
WHERE  student_course.mark < 60

I've tried, but the site says vote up require 15 reputation @Karunakar..:p — mazaj, Nov 14 '14 at 16:36

left join table with two conditions

2 Answers2