Search code examples
mysqldatabasedatabase-table

MySQL Selecting data from multiple different tables in a single query

I have a database of users where I have a primary table with name, address etc. I then have separate tables for the users children, pets, training courses and education results etc.

This users details can all be displayed on the same page and the way I currently do it is by first querying the database for the users main details. Then I query the other tables individually and display this information with loops.

I really just need to know if this is the best way to do it or whether there is a way of doing it with a single query and if there is any advantage?

The basic structure of the tables is:

+----------------+    +-----------------------+
| Users          |    | Children              |
+----------------+    +-----------------------+
|ID |Name |Age   |    |ID |UserRef |Name |Age |
+----------------+    +-----------------------+
|1  |Jim  |53    |    |1  |1       |Joe  |11  |
|2  |Karl |37    |    |2  |1       |Jane |9   |
+----------------+    |3  |2       |Amy  |15  |
                      +-----------------------+

This is a basic version and only shows the user and children table. The other tables are similar to the children table in that they reference the user table and feature multiple records for a single user.

Thanks in advance.

Solution

  • You could do a simple LEFT JOIN of the tables ON users.id = children.userref. However, since it's a one-to-many relationship you'd need to use some GROUP BY magic:

      SELECT users.*, GROUP_CONCAT( children.id ORDER BY children.id ASC ) AS childrens_ids, GROUP_CONCAT( children.name ORDER BY children.id ASC ) AS childrens_names, GROUP_CONCAT( children.age ORDER BY children.id ASC ) AS childrens_ages FROM users LEFT JOIN children ON users.id = children.userref GROUP BY children.userref

    You'd then have to use code to parse/explode the children's IDs, names, and ages. Having said all that, though, this isn't necessarily something you SHOULD do. It's not necessarily faster on the MySQL end, and you see how much extra work you have to do in your back end code. My recommendation is to try it and see how it affects your performance.