Different mysql join query based on user level

Question

I'm seeking an idea or best practice to perform the below:

I have the tables: profile contains basic info (id, email, pass, lvl) profile_user contains (pid, name, age, etc..) profile_mod contains (pid, company_name, money, etc..)

as you can see that I will do left join on two tables to get the whole information, but the join will be all based on the attribute lvl. If lvl=1 it will leftjoin on profile_user and lvl=2 will perform join on profile_mod.

My question is, I don't want to perform a check on every query to get the lvl and then do the leftjoin on the other two tables. I was wondering if there is a best practice for such case.

Answer 1

You can do:

SELECT    *
FROM      profile a
LEFT JOIN profile_user b ON a.id = b.pid AND a.lvl = 1
LEFT JOIN profile_mod  c ON a.id = c.pid AND a.lvl = 2

If lvl is 1, values for fields in profile_user will populate, and values in profile_mod will be NULL.

Likewise, if lvl is 2, values for fields in profile_user will be NULL, but values in profile_mod will be populated.