homepage Welcome to WebmasterWorld Guest from 54.167.174.90
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
MySQL Joins Query
Getting my head around JOINS and WHERE syntax
ManMountain



 
Msg#: 4354408 posted 4:14 am on Aug 23, 2011 (gmt 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:

Select
user.fields,
user_full.fields,
languages.language,
notes.notes,
roles.role
From
user,user_full,languages,notes,roles
Where
user_full.userid = user.id
and
languages.language = user_full.language
and
notes.userid = user.id
and
roles.userid = user.id
and
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,

Ted

 

rocknbil

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



 
Msg#: 4354408 posted 4:03 pm on Aug 23, 2011 (gmt 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:

and
languages.language = user_full.language
and

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

user_full
id|user_id|language
1|1234|3
2|4567|4

languages
id|title
1|Venutian
2|Martian
3|English
4|Portugese

One unique language for each row, then you can do

and
languages.id = user_full.language
and

but . . . something tells me that's already what you're doing, or a lot like it. :-)

ManMountain



 
Msg#: 4354408 posted 5:00 am on Aug 24, 2011 (gmt 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 :)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved