How to join multiple tables related by other tables

Question

I'm developing a site where people can publicate their houses for rent. I'm using php 5.2.0 and MySQL 5+

the publications are stored in a table like this

ta_publications
+---+-------------+------+
|id |    name     | date |
+---+-------------+------+
| 1 | name_001    |  ... |
| 2 | name_002    |  ... |
| 3 | name_003    |  ... |
+---+-------------+------+

I have diferent publications, which have "features" such as "internet", "made service", "satellite tv", etc.

These features might change in the future, and I want to be able to add/remove/modify them, so I store them in the database in a table.

ta_features
+---+-------------+
|id | name        |
+---+-------------+
| 1 | Internet    |
| 2 | Wi-Fi       |
| 3 | satelital tv|
+---+-------------+

which are related to the publications using the following table

ta_publication_features
+---+-------------+----------------+
|id |   type_id   | publication_id |
+---+-------------+----------------+
| 1 |      1      |       1        |
| 2 |      2      |       1        |
| 3 |      3      |       1        |
+---+-------------+----------------+

I think it's pretty easy to understand; There is a publication called name_001 which have internet, wi-fi and satellite tv.

I have the same data-schema for the images, I store them in this table

ta_images
+---+-------------+
|id | src         |
+---+-------------+
| 1 | URL_1       |
| 2 | URL_2       |
| 3 | URL_3       |
+---+-------------+

And use the following table to relate them to the publications

ta_publication_images
+---+-------------+----------------+----------+
|id |  img_id     | publication_id |   order  |
+---+-------------+----------------+----------+
| 1 |      1      |       1        |    0     |
| 2 |      2      |       1        |    1     |
| 3 |      3      |       1        |    2     |
+---+-------------+----------------+----------+

the column order gives the order in wich publications should be displayed when listing a single publication.

Philipp Reichart provided me with a query that will search and get all the publications that have certain features. It works for listing the publications, I can't modified it to return me the data I need.

So I figured I'll run that query and get all of the publications that pass the search criteria and then use another query to list them.

The listing of these publications shall include all publication data (everything on ta_publications)+ all of it's features + the most important (order 0) image src.

I could, for every publication, have two simple querys wich will return, separately, the most important image and all the features it has, but when listing 25 publications per page, it'll be 1 search query + (2 querys per publication * 25 publications) = 51 different querys, clearly not very efficient.

EDIT:

My question is, how can I create a SQL query that, given some publication ids, will return: all publication data (everything on ta_publications) + all of it's features + the most important (order 0) image src

Answer 1

You'll get redundant publication and image data with this one, but here is a way to do it with one query:

   SELECT p.id, p.name, p.date,
           f.id, f.name,
           i.id, i.src
    FROM ta_publications p
    JOIN ta_publication_features pf ON p.id = pf.publication_id
    JOIN ta_features f ON f.id = pf.type_id
    JOIN ta_publication_images pi ON p.id = pi.publication_id 
         AND pi.order = 0
    JOIN ta_images i ON i.id = pi.img_id
    WHERE p.id IN (  -- list of publication ids );

Answer 2

 Select pub.Name as [Publication], f.name as [Feature], i.name as [Image] from ta_publications pub
Join ta_publications_features pf on pf.publication_id = pub.id
Join ta_features f on f.id = pf.type_id
Join ta_publication_images pi on pi.publication_id = pub.id
Join ta_images i on i.id = pi.img_id
Where pub.id = 'appropriate id'
order by i.[order]

If I've followed your structure correctly this should give you the joins you need. and you can add desired columns to the result in the select statment by adding .. hope this helps.