[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

MYSQL LEFT JOIN matching tables including WHERE on LEFT JOINED table is returning 0 results.

I have a two tables...

Categories and Songs...

SELECT *
FROM categories t1
LEFT JOIN songs t2
ON t1.category_id = t2.song_id
WHERE t2.song_status != 'pending'
AND t2.song_status != 'inactive'
AND t2.song_status != 'studio'

problem is that I still want to show the categories even if there are no records... when I remove the WHERE I get what I want but obviously it's checking song_status and it doesn't exist from the LEFT JOIN.

Thanks for the help!
0
brihol44
Asked:
brihol44
1 Solution
 
PortletPaulCommented:
>>"I still want to show the categories even if there are no records"

SELECT *
FROM categories t1
LEFT JOIN songs t2
ON t1.category_id = t2.song_id
        AND t2.song_status != 'pending' 
        AND t2.song_status != 'inactive'
        AND t2.song_status != 'studio'

Open in new window

when you place those conditions in the WHERE clause you create the equivalent of an INNER join.

An alternative to the above s to explicitly allow NULL from the join in addition to the where conditions:
SELECT
      *
FROM categories t1
      LEFT JOIN songs t2
                  ON t1.category_id = t2.song_id
WHERE (
          t2.song_status != 'pending'
      AND t2.song_status != 'inactive'
      AND t2.song_status != 'studio'
       )
      OR t2.song_id IS NULL
;

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now