...

View Full Version : MySQL Query Troubleshoot



RivaCom
09-27-2011, 08:57 PM
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.


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?

Old Pedant
09-27-2011, 09:32 PM
Then you can't use an implicit join, as you are doing.

You *MUST* use an explicit outer join.



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.

RivaCom
09-27-2011, 09:45 PM
Thank you so much!. Been banging my head all day trying to figure this out.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum