Welcome to WebmasterWorld Guest from

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

MySQL Joins Query

Getting my head around JOINS and WHERE syntax

4:14 am on Aug 23, 2011 (gmt 0)

New User

5+ Year Member

joined:July 14, 2011
posts: 19
votes: 0

Hi Again

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:

User ID:1
Name: Bob Smith
Email: bob@bob.com
Various: User data etc
Language: English
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,

4:03 pm on Aug 23, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
votes: 0

That doesn't work? Exactly what is it doing wrong?

You're joining three on the unique user id of the main table, that's all good, the only place it looks like it could get slippery is here:

languages.language = user_full.language

But you've already got

user_full.userid = user.id

so it should bind the results to that user id only, even if there are multiple records for different users 'English'.

What I don't understand though - if you have

languages.language = user_full.language

... it sounds like you already have 'English' in the user_full table (?). If that's the case, no need for another table.

Another scenario might be to leverage the id in languages, like



One unique language for each row, then you can do

languages.id = user_full.language

but . . . something tells me that's already what you're doing, or a lot like it. :-)
5:00 am on Aug 24, 2011 (gmt 0)

New User

5+ Year Member

joined:July 14, 2011
posts: 19
votes: 0

Hi Bill

thanks for your reply, yes on the languages I have a separate table containing just the languages.

It turned out (I hope) that the problem lay in my syntax for the FROM part of the statement. I had the tables in the wrong order :)