Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table that relates a user to client using both their table id's

UsersClients Table

   ---------------------
   | userid | clientid |
   ---------------------
   | 2      | 32       |
   | 3      | 43       |
   | 2      | 24       |
   | 2      | 12       |
   ---------------------

The userid relates to the id field with in my Users Table The clientid relates to the id field with in my Clients Table

I have linked them all up OK.

My question is how can I use this key table to get the details. For instance, I am the user with an ID of 2. By simply doing a query on the above UsersClients Table with the userid of 2, I will simply get the following results 32,24,12.

Now, in order to get the then client details from clients table for each of the returned results, I would query the clients db with something like

   SELECT * FROM Clients WHERE id IN(32,24,12);

But as I have the above UsersClients Table linking up to the relevant tables that I can approach this better, so I don't have to first scrape the UsersClients Table for each client ID then scrape the clients table for the relevant details.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

A simple JOIN query would be in order

SELECT B.* FROM UsersClients A
INNER JOIN Clients B ON A.clientid = B.id
WHERE A.userid = 2;

Give it a Try !!!

share|improve this answer
    
it seems not to throw error but yet it does not seem to display any data either. even with specifying a field etc. Many Thanks for the reply –  Simon Davies Apr 15 '13 at 16:47
    
Changed the JOIN syntax because there is no clientid in the Clients tables. Please try again... –  RolandoMySQLDBA Apr 15 '13 at 16:49
    
yup that worked thought it might be something like that i change everything except the clientid much appreciated –  Simon Davies Apr 15 '13 at 16:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.