CODEWITHSUNDEEP
mysqldatabase
Codded
Codded

Reputation: 1256

MySQL - Exclude row if no row exists from LEFT OUTER JOIN

I have an SQL query which is working ok to an extent.

The LEFT OUTER JOINs work ok, but if there is no record in mdl_modules OR mdl_resource it comes up with NULL.

How can I exclude the records from mdl_course_modules WHERE there is no record in either of the LEFT OUTER JOINs?

I presume i would use ISNULL() but not sure on the correct syntax to incorporate with my query.

Here is my query:

SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", "cm.id AS theid, m.name AS mname, r.name AS resourcename, r.summary AS resourcesummary,
        FROM_UNIXTIME(cm.added, '%D %M %Y') AS dateadded")."
        FROM mdl_course_modules cm 
        LEFT OUTER JOIN mdl_modules m on cm.module = m.id
        LEFT OUTER JOIN mdl_resource r on cm.instance = r.id
        WHERE m.name = 'resource' AND cm.visible = 1
        $scourse
        $sWhere
        $sOrder
        $sLimit

Upvotes: 0

Views: 436

Answers (2)

JLawrence
JLawrence

Reputation: 192

WHERE m.name = 'resource' AND cm.visible=1 AND (m.id IS NOT NULL OR r.id IS NOT NULL)

Upvotes: 1

Jon
Jon

Reputation: 437336

Normally this would be accomplished by switching to an INNER JOIN, but INNER JOIN in MySql is equivalent to a CROSS JOIN so that won't help practically.

To solve the issue, add the desired WHERE condition, using ISNULL:

SELECT ...
    FROM_UNIXTIME(cm.added, '%D %M %Y') AS dateadded")."
    FROM mdl_course_modules cm 
    LEFT OUTER JOIN mdl_modules m on cm.module = m.id
    LEFT OUTER JOIN mdl_resource r on cm.instance = r.id
    WHERE m.name = 'resource' AND cm.visible = 1
        && (!ISNULL(m.name) || !ISNULL(r.name))

Upvotes: 1

Related Questions