Dazman2011 Posted November 29, 2011 Share Posted November 29, 2011 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 Quote Link to comment Share on other sites More sharing options...
Dazman2011 Posted November 29, 2011 Author Share Posted November 29, 2011 Ok, I think found my own solution now ... I think table aliases are what I needed to do this effectively. Quote Link to comment Share on other sites More sharing options...
mikosiko Posted November 29, 2011 Share Posted November 29, 2011 the design for your table jobs is calling for a redesign .... you should have a third table with the relationship between jobs and users Quote Link to comment Share on other sites More sharing options...
Dazman2011 Posted November 29, 2011 Author Share Posted November 29, 2011 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. Quote Link to comment Share on other sites More sharing options...
mikosiko Posted November 29, 2011 Share Posted November 29, 2011 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 Quote Link to comment Share on other sites More sharing options...
Dazman2011 Posted November 30, 2011 Author Share Posted November 30, 2011 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? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.