mysql if/else scenario

Ok, here's a fun one. I have 2 tables: tbl_notes, tbl_notes_categories

Simply, the tbl_notes has a categoryid, and I correlate the 2 tables with that ID. So, nothing too complicated.

I force users to choose a category, from a dropdown input, and stop them from submitting if they don't select something.

However, I want to change this, primarily for learning JOINs and how far I can go with them.

Sooooooo, I am not going to force a user to select a category, and instead, I will default the categoryid to zero, in the tbl_notes. (most users will select a category, but this is for other instances)

In the query, I 开发者_运维知识库am locked to showing only the notes that have a categoryid that exists in the tbl_notes_categories table. But, I would like to have a condition if the categoryid is not recognized OR is equal to zero, then specify another String. Like "--Unassigned--", or "--Category does not exist--"

Here's my original query:

SELECT n.notesubject, c.categoryname 
FROM `tbl_notes` n, `tbl_notes_categories` c 
WHERE n.categoryid = c.categoryid 

This will not let me see the notes without a categoryid, so I pulled this one:

SELECT n.notesubject, c.categoryname
FROM `tbl_notes` n
 LEFT JOIN `tbl_notes_categories` c ON n.categoryid = c.categoryid

And that helps, but I'm stuck at the 'condition' of displaying alternate text, in the case of a missing category record from the categories table.

In MySQL you can use IFNULL:

SELECT
    n.notesubject,
    IFNULL(c.categoryname, 'Unknown') AS categoryname
FROM tbl_notes AS n
LEFT JOIN tbl_notes_categories AS c
ON n.categoryid = c.categoryid

This will work if the category is not found, but it will also work if the category id is zero assuming that you don't have a matching row in your category table because then it will also not be found. If for some reason you do want a row in the categroy table with id zero then you can just set its name to 'Unknown'.

Note that IFNULL is MySQL specific. The function COALESCE will also work and is supported by more databases.

For IF/ELSE statements in general in MySQL can use IF or for a more general solution use a CASE expression: CASE WHEN condition THEN expr1 ELSE expr2 END.