What is the best way to retrieve multiple data based on given ID's in MySQL?

Question

I have three tables

Table1: Places (a place can have multiple contacts)
Columns: place_id, place_name, ...

Table2: Contacts (a contact can visit multiple places)
Columns: contact_id, contact_name, ...

Table3: PlacesContacts
Columns: id, place_id, contact_id

then I want to get all users from a specific place then I can do something like

select * from PlacesContacts where place_id = 3

I will get all contact_id's I need but also I want to get all the contact_id's data from Contacts table.

What is the best possible way to achieve this? Any help would be much appreciated.

Answer 1

Use a join:

SELECT pc.*, c.*
FROM PlacesContacts pc
INNER JOIN Contacts c
    ON pc.contact_id = c.contact_id
WHERE pc.place_id = 3;

Answer 2

your contact_id from PlaceContacts is from the Contacts table, isn't it? then you can join those 2 table

select PlacesContacts.*,Contacts.* from PlacesContacts
inner join Contacts on Contacts.contact_id=PlacesContacts.contact_id
 where place_id = 3

Answer 3

You can use simple INNER JOIN statement:

SELECT PlacesContacts.*, Contacts.* 
FROM PlacesContacts 
INNER JOIN Contacts 
ON PlacesContacts.contact_id = Contacts.contact_id
WHERE PlacesContacts.place_id = 3;

Thanks

Answer 4

I am assuming that the relation between Tables - Places and Contacts is m:n. (Check #1 if you have no idea what is a m to n relationship) Then Tim Biegeleisen's answer will help you.

Else if it is a 1:n relationship. Maybe you should add place_id into the Contacts table and remove the PlacesContacts.

1 - Meaning of "n:m" and "1:n" in database design