MySQL Selecting data from multiple different tables in a single query

Question

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.

Clearify your question a little bit more and share your desired result. — Ankit Sharma, Apr 16 '12 at 14:15
It is really simple to do. I suggest you first write down which columns are in question and from which table they belong. Then write down the outputs of what you would like the end result table to look like. Knowing the inputs and outputs allow you to then form and write an SQL statement that will give the desired output. You first just need to know exactly what you want the SQL query to do before you write the statement so everything works as you expected it to Check my answer and let me know how it goes — Pavan, Apr 16 '12 at 14:29

score 0 · Answer 1 · answered Apr 16 '12 at 14:17

Here's a good query to start with:

SELECT * FROM Users RIGHT JOIN Children ON Users.ID = Children.UserRef

Note that this will give you two values called "Name" - which is why you need to use aliases:

SELECT Users.Name as ParentName, Children.Name as ChildName FROM Users RIGHT JOIN Children ON Users.ID = Children.UserRef

You can add any other fields you need using the above format.

As for whether or not it's better to use this, every method has its advantages and disadvantages. When you create a script with bigger, more advanced queries that do what you want (as opposed to many smaller queries) your script will run faster because instead of there being 1 + (number of parents) queries going to the server and back, there will only be exactly one query. However, poorly organized queries can cause debugging trouble later when you come back to it and don't remember why you made it the way you did.

Anyone care to share why this was downvoted? I think this was a good response, and he covered all the bases. — tcole, Apr 16 '12 at 14:22
Another user had a terse comment which got down-voted, so I suspect it was retribution. Thanks for your support. — Tim, Apr 16 '12 at 14:24

score 0 · Accepted Answer · edited Feb 03 '22 at 17:49

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.

I think this is the way to go. I will give it a try and see how it affects performance. — Sean, Apr 16 '12 at 15:11

MySQL Selecting data from multiple different tables in a single query

2 Answers2