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
    New to the CF scene
    Join Date
    Sep 2011
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts

    MySQL Query Troubleshoot

    I'll try to explain this as best as possible. I have 3 tables(phpbb) I'm trying to pull 3 fields, from the 3 tables.

    Table 1 -> user_id,username,rank_id
    Table 2 -> rank_id,rank_title
    Table 3 -> user_id, field1

    Below is the query I use, I get the correct amount of users, but it stops input for field1 after the first field1 record. Not every entry will have a field 1 entry in the third table. So for example out of 25 entries, only 3 entries will have a entry in table3.

    Code:
    SELECT DISTINCT u.username, r.rank_title, IF(f.user_id = u.user_id, f.pf_field1, "")
    FROM (phpbb_users u, phpbb_ranks r, phpbb_profile_fields_data f)
    WHERE (u.user_rank BETWEEN "1" AND "6")
    AND u.user_rank = r.rank_id
    ORDER BY CASE r.rank_id
    WHEN "2" THEN 1
    WHEN "6" THEN 2
    WHEN "4" THEN 3
    WHEN "3" THEN 4
    END
    Any Ideas?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,169
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Then you can't use an implicit join, as you are doing.

    You *MUST* use an explicit outer join.

    Code:
    SELECT DISTINCT u.username, r.rank_title, IFNULL(f.pf_field1, '') AS field1
    FROM phpbb_users AS u INNER JOIN phpbb_ranks AS r
         ON u.rank_id = r.rank_id
    LEFT JOIN phpbb_profile_fields_data AS f
         ON u.user_id = f.user_id
    WHERE u.user_rank BETWEEN 1 AND 6
    ORDER BY ( CASE r.rank_id
                    WHEN 2 THEN 1
                    WHEN 6 THEN 2
                    WHEN 4 THEN 3
                    WHEN 3 THEN 4
                    END )
    I removed the "..." around all your rank_id and user_rank values because *SURELY* those fields are INT. You should *NOT* use "..." or '...' around INT values. MySQL is sloppy enough to allow it, but it incurs a minor performance penalty and isn't good practice.

    QUESTION:
    What ORDER BY are you expecting when phbb_ranks.rank_id is *NOT* 2,6,4, or 3? (That is, when it is 1 or 5.) You don't have any ELSE for the CASE so the ORDER BY is then undefined.
    Last edited by Old Pedant; 09-27-2011 at 09:34 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Sep 2011
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you so much!. Been banging my head all day trying to figure this out.


  •  

    Posting Permissions

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