CODEWITHSUNDEEP
mysql
Jessica Chambers
Jessica Chambers

Reputation: 1316

MySQL request on joining table: keep secondary table separate

I have 3 MySQL tables, housing, hp (joining table) and photos.

I want to be able to link each housing item to the list of photo items that are connected to it via hp, ideally in one MySQL request.

Currently I have tried the following:

SELECT housing.id, housing.title, photos.url
FROM housing
JOIN hp ON housing.id=hp.housing_id
JOIN photos ON hp.photo_id=photos.photo_id ;

But using this, if one housing element has 3 photos elements, that housing element is returned 3 times.

I'm using PHP to send/handle the queries, so is there a way to return an array of photos elements for each housing element?

Upvotes: 0

Views: 29

Answers (1)

forpas
forpas

Reputation: 164174

With group_concat():

SELECT housing.id, housing.title, group_concat(photos.url) as url
FROM housing
JOIN hp ON housing.id=hp.housing_id
JOIN photos ON hp.photo_id=photos.photo_id 
GROUP BY housing.id, housing.title

You will get a comma separated list of the photos.
If there is a case there are not any photos for a housing item then change to LEFT join.

Upvotes: 2

Related Questions