Basic SQL query problem

[Dazman2011]

Hi Everyone,

 

I have a basic SQL query issue that I can't seem to get my head around. I'm self-taught (from books and internet) when it comes to MySQL and PHP so I'm not sure if I'm thinking about this correctly or not!

 

I have two tables (and others that have no importance here), one containing job records and one containing users. In each job record there are IDs for several users (so several people are referenced for each job) as well as other miscellaneous fields that contain information about the job. I'd like to pull out a list of jobs and simply populate the user IDs in each record with the actual names of the users. Normally I'd use a standard join to do this with a condition to link the user id to the users table id, but because there are three user IDs all referencing the same user table it's got me kind of stumped. Is there a way to do this without having a seperate query to pull out the name of each user? Do I need another 'link' table to link the users in order to achieve that relationship - it would seem a bit cumbersome and I'm still struggling to see how that would work (I've got a real mental block on this one!).

 

Can anyone suggest the correct and most efficient method to do this? Maybe I need to restructure my database?

 

------------------------------------------

jobs

------------------------------------------

 

user1

 

user2

 

user3

 

(other fields)

 

------------------------------------------

users

------------------------------------------

 

id

 

first_name

 

surname

 

 

 

 

 

 

I'm on MySQL server 5.5 if that helps

 

Many thanks,

Darren

[Dazman2011]

Ok, I think found my own solution now ... I think table aliases are what I needed to do this effectively.

[mikosiko]

the design for your table jobs is calling for a redesign .... you should have a third table with the relationship between jobs and users

[Dazman2011]

Thanks for replying mikosiko,

 

I initially was thinking along the same lines, however my issue with going down that route is that is complicates something that is essentially a very simple relationship, plus it doesn't help me pull out the data very easily when it comes to the conditions needed to link the IDs to the link tables and ultimately the users table.

 

If we just take two of the users as an example, say one is the 'job owner' and the other is an 'alternative contact', it would mean building two separate link tables, one for owners that would look a bit like this :-

 

owners

-----------------------------------------------

job_id          |            user_id

-----------------------------------------------

 

 

and one that would look like this:-

 

alt_contacts

------------------------------------------------

job_id          |            user_id

------------------------------------------------

 

so essentially two identical link tables with different names, meaning we remove the owner_id and alt_contact_id fields from the job table.

 

The user table might look something like this:-

 

users

------------------------------------------------

id  |  first_name  | surname  | status

------------------------------------------------

 

So now we've got the users connected to the table using two seperate tables, however when it comes to writing the conditions to pull out the surname of the owner users and then the conditions to pull out the surname for the alternative contact, I still have no way of identifying which is which.

 

Normally I'd do something like:-

 

$result = mysql_query("SELECT users.surname AS owner_surname 
                                         FROM jobs, owners
                                      WHERE owners.job_id = jobs.id
                                           AND owners.user_id = users.id;");

 

But now if I try to link the other link table in it might look like this:-

 

$result = mysql_query("SELECT users.surname AS owner_surname,
                                                   users.surname AS alt_contact_surname
                                         FROM jobs, owners
                                      WHERE owners.job_id = jobs.id
                                           AND owners.user_id = users.id
                                           AND alt_contacts.job_id = jobs.id
                                           AND alt_contacts.user_id = users.id;");

 

...and there is my problem ... how do I differentiate between the the two 'users.surname' fields that I get from the two link tables, bearing in mind that they are linking to exactly the same users table (which is a requirement)

 

So the solution I've ended up with is this (no new link tables created - just the users and jobs tables needed):-

 

$result = mysql_query("SELECT owners.surname         AS owner_surname,
                                                   alt_contacts.surname AS alt_contact_surname

                                         FROM jobs, 
                                                   users AS owners,
                                                   users AS alt_contacts

                                      WHERE owners.id = jobs.owner_id
                                           AND alt_contacts.id = jobs.alt_contact_id;");

 

Which I think is quite an elegant way of dealing with it. I'm open to other ideas though if you think that way is inefficient, or if you can see an obvious error in my thinking on the link tables.

 

 

 

[mikosiko]

I believe that you don't need 2 tables (owners & alt_contact)... the user rol should be an attribute of the relationship table (let call it Job_users)

 

job_users

job_id

user_id

user_rol  (it can be a ENUM with the defined roles.. O=owner, A=Alternate, etc..etc)

 

in your current solution if you need in the future add more user's roles you will need to modify you table jobs to add other column.. j.m.h.o

[Dazman2011]

Thanks mikosiko,

 

I see what you mean. I'll give this some thought as it may end up being beneficial to make the users linked to a job more flexible. At the moment I'm working to a very rigid structure, but there's always a chance it may evolve at a later stage. I think there may still be some unnecessary complexity in using that for my solution, as surely it would mean I'd have to get into the realms of aggregate functions to bring the different users into one row result? Or maybe break it into two queries?