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:

I'd like a select to show me this:

What I've tried so far:

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:

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!

CodePudding user response：

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.