MySQL IFNULL() with multiple tables isn't work

问题:

I'm using this query in my code, but it doesn't give me a result

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

I know it's because s.hostId can be NULL.

I've found the IFNULL()-method, and used it like this, but that didn't work.

SELECT s.day, s.hour, IFNULL(h.name, "") hostname

Can somebody help me please? :S

P.S.: I know I could use the sprintf with only 1 real_escape (because it's 2 times the same value), but if I replace the %s with %1$s like I should be with placeholders I get an error :S

So if anybody can help me with that you're also welcome :-)

回答1:

You want to include records where s.hostId can be null? If that's the case, then fiddling with the fieldlist is utterly pointless. The decision to include/exclude rows is done in the WHERE clause.

You probably want

 WHERE ...
     (s.hostId = %s OR ISNULL(s.hostId))

回答2:

You should use IFNULL for MySql. ISNULL is for Microsoft's sql

IFNULL(h.name, "")