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))

Is day ever `NULL`? Any `NULL` in the subselect will cause the in to return `NULL` if no match is found. — Arth, Sep 23 '14 at 18:08
I don't think mysql has an ISNULL function. It should be s.hostId IS NULL. — Jeff, Sep 23 '14 at 18:30
**"Isn't working"** and **"didn't work"** are *not* a clear statement of the behavior you are observing. An error message? Not enough rows returned? Too many rows, or wrong rows returned? What result set are you trying to achieve? Some good folks here are going to give you some suggestions, but no one is going to be able to **answer your question** because you **haven't asked a question.** — spencer7593, Sep 23 '14 at 18:36
@Jeff: The ISNULL-function is working, but the query still gives me no results — MichaelDeBoey, Sep 24 '14 at 15:27
@spencer7593: My query gives me no rows if I have a show with no host in it — MichaelDeBoey, Sep 24 '14 at 15:27
@MichaleDeBoey: I have a query similar to that, that returns no rows under certain conditions. Were you meaning to ask: "Why are no rows being returned by my query?", or were you just giving a status report? If you want help with this, if you aren't able to articulate a question, then at least make the effort to setup a SQL Fiddle [http://sqlfiddle.com](http://sqlfiddle.com) that demonstrates the behavior you are describing. — spencer7593, Sep 25 '14 at 01:44

Arth · Accepted Answer · 2014-09-23T18:25:30.593

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.

edited Sep 23 '14 at 18:25

answered Sep 23 '14 at 18:17

Arth

12,789
5
37
69

+1. @Arth: as you point out, we're just guessing at what OP is trying to achieve, what behavior he's observing, and how that differs from the results he's expecting to be returned. – spencer7593 Sep 23 '14 at 18:40
Like I posted [here](http://stackoverflow.com/questions/26001410/mysql-ifnull-with-multiple-tables-still-isnt-working#comment40755550_26001410): My query gives me no rows if I have a show with no host. But Thanks for the help! The `LEFT JOIN`worked for me! Maybe because I used the logical equivalent? – MichaelDeBoey Sep 24 '14 at 15:30
Cool, glad it worked! It was probably something to do with the IN condition not behaving as expected. – Arth Sep 24 '14 at 15:40

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

1 Answers1