MySQL IFNULL() with multiple tables still isn't working

Question

I know I already asked this question here but haven't had a good answer so I try it again and this time I hope I can give enough information for you guys.

Before I ask my question I'll show you how I created (the usefull part of) my database:

CREATE TABLE host(
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        naam VARCHAR(40) NOT NULL,
        isActief BOOLEAN NOT NULL DEFAULT TRUE,

        UNIQUE unq_host_naam(naam)
);


CREATE TABLE shows(
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        naam VARCHAR(30) NOT NULL,
        facebook VARCHAR(50) DEFAULT NULL,
        twitter VARCHAR(50) DEFAULT NULL,
        isNonStop BOOLEAN NOT NULL DEFAULT FALSE,
        showProgrammaInMenu BOOLEAN NOT NULL DEFAULT TRUE,
        isActief BOOLEAN NOT NULL DEFAULT TRUE,

        UNIQUE unq_shows_naam(naam)
);


/* The following tables (day and hour) were created because MySQL doesn't have CHECK-constraints*/
CREATE TABLE day( id INT NOT NULL PRIMARY KEY );
INSERT INTO day
        VALUES (1), (2), (3), (4), (5), (6), (7);
CREATE TABLE hour( id INT NOT NULL PRIMARY KEY );
INSERT INTO hour
        VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23);


CREATE TABLE schedule(
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        dag INT NOT NULL,
        uur INT NOT NULL,
        showId INT,
        hostId INT,

        FOREIGN KEY fk_schedule_day(dag) REFERENCES day(id),
        FOREIGN KEY fk_schedule_hour(uur) REFERENCES hour(id),
        FOREIGN KEY fk_schedule_shows(showId) REFERENCES shows(id),
        FOREIGN KEY fk_schedule_host(hostId) REFERENCES host(id),

        UNIQUE unq_schedule_dag_uur(dag, uur)
);

I'm trying to run this query:

$query = sprintf("SELECT s.dag, s.uur, h.naam hostName
                FROM schedule s, host h
                WHERE dag IN (SELECT dag
                                FROM schedule
                                WHERE showId = %s)
                    AND s.hostId = h.id,
                    AND s.showId = %s
                ORDER BY dag, uur",
            mysqli_real_escape_string($con, $id),
            mysqli_real_escape_string($con, $id));

I know it isn't working because hostId CAN be NULL, so I tried to fix it by using the IFNULL()-method of MySQL:

SELECT s.dag, s.uur, IFNULL(h.naam, "") hostname

But that didn't work. Also the option that was mentioned here isn't working because that just neutralizes itself...

$query = sprintf("SELECT s.dag, s.uur, h.naam hostName
                FROM schedule s, host h
                WHERE dag IN (SELECT dag
                                FROM schedule
                                WHERE showId = %s)
                    AND (s.hostId = h.id OR ISNULL(s.hostId))
                    AND s.showId = %s
                ORDER BY dag, uur",
            mysqli_real_escape_string($con, $id),
            mysqli_real_escape_string($con, $id));

This is the same as I use it without the

AND (h.id = s.hostId OR ISNULL(s.hostId))

Answer 1

Your query is a bit of a mess.. what are you trying to do?

Your subquery selects the day from the schedule where showId is the passed in variable and compares it to the day of another schedule filtered by the same showId.. this is meaningless.

Why have you ordered the subquery?

Also, as I mentioned in the comments, if day is ever NULL an IN will return NULL (false for a condition) if the LHS is NULL or if there is no match in the RHS and one of the values is NULL.

You have also used an implicit join which makes it harder to tell what is going on. It looks like you want a LEFT JOIN, to return schedules without a host.

It seems that you have tried to write the logical equivalent of:

   SELECT s.day, s.hour, COALESCE(h.name,'') hostName
     FROM schedule s
LEFT JOIN host h
       ON h.id = s.hostId
    WHERE s.showId = %s
 ORDER BY s.day, s.hour

Also please expand on 'doesn't work'; Errors? Doesn't return anything? Doesn't return expected results? Without explaining what you are trying to do.. 'doesn't work' is useless.