mysql query for multiple tables

Question

I have 3 three tables as follows a user can have many contacts and a contact can be listed by many users...

//user table

user_id | username|password|fname|lname|email|contactnumber

//contact table

contact_id | fname | lname |email|contactnumber

//user_contact table

user_id |contact id | relationship type |relationship state

My query must display all the contacts that is link to the selected user ...any advise will be helpful

so it will look like this

Result:

user fname | user lname | email address | contact number of user | contact first name | contact last name | relationship type | relationship state

What have you tried so far? Any example queries you have built? If not, you should learn about joins - http://stackoverflow.com/questions/6294778/mysql-quick-breakdown-of-the-types-of-joins should help. — SamV, Nov 03 '13 at 22:01

score 0 · Accepted Answer · answered Nov 04 '13 at 00:58

Correct me if I understand your question wrong: so the user table and contact table has a many to many relation? then you can do

select u.fname,u.lname,u.email,u.contactnumber,c.fname,c.lname,uc.relationship_type,uc.relationship_state 
from user as u
inner join user_contact as uc on u.user_id=uc.user_id
inner join contact as c on uc.contact_id=c.contact_id
where u.user_id=<userId>

mysql query for multiple tables

1 Answers1