Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Aug 2004
    Location
    The US of A
    Posts
    767
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Resolving NULL returns on a joined query

    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.

    Code:
     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:

    Code:
     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.

  • #2
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:

    Code:
    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.

  • #3
    Regular Coder
    Join Date
    Aug 2004
    Location
    The US of A
    Posts
    767
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you. I'm horrible with joins. I dunno the difference between inner and the different outer joins.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •