in the aftermath of my tactic to strategically sidestep an understanding of LEFT; RIGHT, INNER and OUTER joins for the past 10 years, of course it is nonsense to avoid it and finally the time has come when I need to actually understand this stuff. Wikipedia has been helpful, but not helpful enough.
Hopefully some of you good folks here can point me in the right direction with this SELECT query. I am getting there, but a few pointers at this stage would be muchly appreciated.
So, I have a main USER table which I am normalizing to the best of my understanding.
Firstly, I split the USER table into 2 tables - USER and USER_FULL. The former table contains the most commonly accessed user info such as ID, USERNAME, USERTYPE etc. All info that is accessed occasionally is now moved into the latter table. USER_FULL contains a USERID field which contains the ID value from USER, in order to connect the 2 tables.
All records in USER should have a corresponding record in USER_FULL, since various fields in both USER and USER_FULL are populated at user registration. So far so good, and I am having no troubles running selects joining these 2 tables.
Confusion begins to appear when I go a bit further.
In the USER_FULL table for example, I had a field LANGUAGE which contained repeating values e.g. "English". So I took this data out, created a LANGUAGES table with an ID and LANGUAGE field which I hope to link in. All users are assigned a language at registration, so there should always be a connection there. The confusion arises because I am joining USERS_FULL to USERS, then trying to join LANGUAGES to USERS_FULL.
Also, there was a NOTES blob field on the USER record, which is rarely viewed / used and would otherwise slow things down. So I also created a separate USERS_NOTES table, with ID, USERID, and NOTES fields. In this scenario, not all users will have notes about them, so not all USER records will have a corresponding record in USERS_NOTES.
The final bit of confusion is that each user can have various roles assigned, such as EDITOR, MODERATOR, CLIENT etc - the user may have no roles, 1 role or many different roles, and more roles need to be added in the future. This role info is accessed often, so needs to be queried efficiently. I think what I have done is the right thing - I have created a ROLES table, containing an ID and a ROLE. I then created a table USER_ROLES with ID,USERID,ROLEID, with appropriate indexes, linking USER to the ROLE list / table. Building this last bit into the query sails right over my head at the moment.
So approximately the query I need is something like this:
user_full.userid = user.id
languages.language = user_full.language
notes.userid = user.id
roles.userid = user.id
user.id = 1
ending up with something like for example:
Name: Bob Smith
Various: User data etc
Notes: (if present) etc etc
Roles: (if present) Moderator,Client,Photographer
So far I have tried various permutations on LEFT JOIN, but nothing is working as intended.
Could anyone lend a hand formulating this query? I am hoping this will clarify a lot of the ? in my head currently.
Many thanks as usual,