#1
  1. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    96

    Join returns null for all fields


    My two tables:

    admin_people
    -------------
    pkey
    fname
    lname
    group_name


    admin_creds
    ------------
    people_key (foreign key = admin_people.pkey)
    menu_key (foreign key = admin_menu.pkey)


    What I'm trying to do is capture all the admin_people info and all the menu_keys that person is associated with. Thing is, not all people will have a record in the admin_creds table. When they do, this query works fine:

    PHP Code:
    SELECT CONCAT(admin_people.fname,' ',admin_people.lname) AS fullnameadmin_people.group_nameGROUP_CONCAT(admin_creds.menu_key SEPARATOR '~'FROM admin_people RIGHT JOIN admin_creds ON admin_people.pkey=admin_creds.people_key 
    When querying for an admin_pkey that doesn't exist in admin_creds.people_key, I get null values for everything. Sounds like I'm using the wrong join, but have no idea.

    Any thoughts?

    Oh, and I'd also like this to grab one specific record at a time. Can I stick a WHERE in there somewhere?
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,436
    Rep Power
    1688
    You've a GROUP_CONCAT but no GROUP BY, which is not causing your issue but is, I believe, not right.
    CONCAT returns NULL if any constituent part equals NULL - look at CONCAT_WS instead?
    Any WHERE would 'bolt on' the end. Depending on what you are filtering on an additional condition on a JOIN might be better.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    96
    Hm. I'm not sure I'm doing this right.

    I've stripped out all the CONCAT statements and still it won't return what I need.

    Code:
    SELECT admin_people.fname,admin_people.lname, admin_people.group_name, admin_people.password, admin_people.last_login, admin_creds.menu_key  FROM admin_people, admin_creds WHERE admin_people.pkey=1 AND admin_creds.people_key=admin_people.pkey GROUP BY admin_people.fname
    Using CONCAT_WS and adding a GROUP BY didn't change a thing. No errors, but no results either.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    why would you add a GROUP BY here? you don't seem to be aggregating anything

    by the way, could you please format your queries so that they're not shown on one single humoungously long line, thanks

    also, use explicit JOIN syntax, you will learn to love it
    Code:
    SELECT admin_people.fname
         , admin_people.lname
         , admin_people.group_name
         , admin_people.password
         , admin_people.last_login
         , admin_creds.menu_key  
      FROM admin_people
    INNER
      JOIN admin_creds 
        ON admin_creds.people_key = admin_people.pkey
     WHERE admin_people.pkey=1 
    GROUP 
        BY admin_people.fname
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    96
    Thanks, Rudy.

    My original query was using CONCAT and GROUP_CONCAT, but adding a GROUP BY to that didn't affect my results.

    Following SimonJM's thoughts on that, I stripped out all the CONCAT statements to see if that helped, but it didn't. Sorry to say, even using your example doesn't return any results.

    To make sure I'm not expecting the impossible, my query does work when I change the admin_people.pkey=1 to admin_people.pkey=2.

    The person associated with pkey 1 has a record in the admin_people table, but no related data in the admin_creds table. Make sense?

    PS--I've been using LEFT and RIGHT joins here. Didn't try an INNER until yours. Will also try to format the queries better. Sorry. Just copying/pasting from my code.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Frank Grimes
    Sorry to say, even using your example doesn't return any results.
    that wasn't my example, that was your query, reformatted

    and now i will show you the benefit of using explicit JOIN syntax

    change INNER to LEFT OUTER and run it again

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    96
    Originally Posted by r937
    that wasn't my example, that was your query, reformatted
    Ack. Sorry.

    LEFT OUTER JOIN, huh? Well, that did the trick. I do normally use JOINS, but am still coming to terms with all of them. Thank you for this.

    Also, if I use CONCAT and GROUP_CONCAT as I do in my OP, do I need a GROUP BY?
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Frank Grimes
    Also, if I use CONCAT and GROUP_CONCAT as I do in my OP, do I need a GROUP BY?
    CONCAT, no

    GROUP_CONCAT, yes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo