mysqlsqlsubquerycaseiif

Should I use CASE WHEN or IIF? Are subqueries a possible part of the solution?

I'm using My SQL (latest version) and I have the following table:

CREATE TABLE COURSES (
IDCOURSE INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR (30) NOT NULL, 
HOURS INT,
PRICE FLOAT (10,2) NOT NULL, 
ID_PREREQ INT
);

ALTER TABLE COURSES ADD CONSTRAINT FK_PREREQ
FOREIGN KEY (ID_PREREQ) REFERENCES COURSES (IDCOURSE);

What I want do is: select the columns NAME, HOURS, PRICE and ID_PREREQ AS REQUISITES. Whenever ID_PREREQ is NULL, I want to read "NO REQUISITES". If else, then I want to read whatever's in the associated NAME column.

For example:

If this is my table:

IDCOURSE NAME HOURS PRICE ID_PREREQ
1 RELATIONAL DB 20 400.00 NULL
2 BUSINESS INTELLIGENCE 40 800.00 1
3 ADVANCED DB 20 600.00 2
4 PROGRAMMING 20 400.00 NULL

I'd like a select to show me this:

NAME HOURS PRICE REQUISITES
RELATIONAL DB 20 400.00 NO REQUISITES
BUSINESS INTELLIGENCE 40 800.00 RELATIONAL DB
ADVANCED DB 20 600.00 BUSINESS INTELLIGENCE
PROGRAMMING 20 400.00 NO REQUISITES

What I've tried so far:

enter image description here

With this solution, I came across two different problems:

1. How to show the NAME related to the correct ID_PREREQ?

I managed to write the following code:

SELECT CASE WHEN ID_PREREQ IS NOT NULL
    THEN NAME
    ELSE 'NO REQUISITES'
END
FROM COURSES;

But as you can see, I'm merely repeting the name column in the REQUISITES column, instead of getting the associated value I want. I was also unable to give the result column its proper name (REQUISITES).

2. How to select more columns besides the REQUISITES column?

I've tried this:

enter image description here

But for some reason I got a syntax error.

Finally, I also tried the IIF Statement because the syntax seemed easier, but I got an ERROR 1305: FUNCTION does not exist.

I don't understand why is that, considering that I'm using the latest MySQL version and apparently that IIF statement was released in 2012.

Can somebody please help me?

Thank you!

Solution

  • If you need to extract the corresponding prerequisite along with the course, you need to apply a SELF JOIN operation. Among the join kinds, you could use a LEFT JOIN to keep the courses with no prerequisites. Then the COALESCE function will allow you to substitute your NULL values with a string of your choice (like "NO REQUISITES").

    SELECT c1.IDCOURSE,
       c1.NAME,
       c1.HOURS,
       c1.PRICE,
       COALESCE(c2.NAME, 'NO REQUISITES') AS REQUISITES
FROM      COURSES c1
LEFT JOIN COURSES c2
       ON c1.ID_PREREQ = c2.IDCOURSE 
ORDER BY IDCOURSE

    Check the demo here.

    Side Note: MySQL's IIF function is named IF.