Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : MySQL Query Troubleshoot

[RivaCom]

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?

[foobic]

Have you tried using JOINs? From the description it sounds like you could get what you want with an inner join on table 2 and a left (outer) join on table 3.