Resolving NULL returns on a joined query

[Kurashu]

I have a database arranged where I have several tables, all of them related one way or another.

The main table I querying information from is players. It contains the id, username, password, and some other data. It also has two fields labeled hijink and quote. These fields can be null, but if they are my select (see below) will skip over them. So I tried to use COALESCE to avoid this, but that failed as well.

SELECT p.name, p.joined, p.email, h.name, q.quote FROM players p, hijinks h, quotes q WHERE p.hijink = h.id AND p.quote = q.id;

If the Hijink and Quote fields in the players table is null then it will return null. So I thought that using COALESCE would be a good idea:

SELECT p.name, p.joined, p.email, COALESCE(h.name, 'none') AS hijink, COALESCE(q.quote, 'none') AS quote FROM players p, hijinks h, quotes q WHERE p.hijink = h.id AND p.quote = q.id;

That also returns NULL, much to my dismay.

Am I simply going to have to set an empty row in my hijinks and quotes tables and make their values the default for the hijinks and quotes tables? Or is there something that I'm doing wrong and I'm failing to see?

Hope to get some help soon, thanks in advance.

[Kid Charming]

The problem is that you're using an INNER JOIN; this will only retrieve records from the joined tables that have matching values in whatever field you're joining them on. You need to use LEFT joins to get NULL values for fields that don't have matching joined fields:


SELECT
p.name
, p.joined
, p.email
, h.name
, q.quote
FROM
players p
LEFT JOIN
hijinks h
ON
p.hijink = h.id
LEFT JOIN
quotes q
ON
p.quote = q.id


Then, if you want a specific NULL message, you can use COALESCE() to get it.

[Kurashu]

Thank you. I'm horrible with joins. I dunno the difference between inner and the different outer joins.