Mysql select from multiple tables

1. Use GROUP BY food to SELECT From Multiple Tables
2. Use JOIN to SELECT From Multiple Tables in MySQL
3. Use GROUP_CONCAT() and Manipulate the Results in MySQL

This tutorial shows you how to query SELECT from multiple tables in a single script with the use of MySQL.

Let’s demonstrate one scenario:

SELECT name, price, details, type,  FROM food, food_order  WHERE breakfast.id = 'breakfast_id'

Now let’s imagine sample tables for each FROM entry:

• food

• food_menu

Basing on the tables above, we can see that Eggs have 3 photos, Ice cream also has 3, while Ramen has 2. The output we want to produce is an aggregated table of food and food_menu, display all the food together and combining them with the corresponding photos in the menu.

If we query this:

SELECT name, price, options, photo 
FROM food, food_menu 
WHERE food_id = '1'

The result would be this:

The data is duplicated because there are multiple rows of food_menu related to food. In this case, there are 3 photos in food_menu that are tied directly to Eggs.

It is not possible for a simple query to join all the entries in food_menu in one single row, since they are all considered as separate entities that are related to table food.

If you want to query food and food_menu at the same time in a single row, then here are a few options to do that.

Use GROUP BY food to SELECT From Multiple Tables

This approach uses GROUP BY to aggregate both tables in one result. The drawback, however, is you will only get the first instance of food_menu since we’re forcing the results to be unique.

Below is the query to GROUP BY food table:

SELECT name, price, options, photo
FROM food, food_menu
WHERE food_id = '1' 
GROUP BY food_id

It would then display the following result:

We now have satisfied the condition, although only one photo is returned, which is the first instance of food_menu found by the query.

Use JOIN to SELECT From Multiple Tables in MySQL

This approach makes use of SQL’s JOIN or RIGHT JOIN command.

Instead of having 2 FROM conditions in the script, we JOIN food_menu based on its food_id foreign key. We aliased f for food and fm for food_menu

SELECT f.name, f.price, f.options, fm.food_menu
FROM food AS f
JOIN food_menu AS fm ON fm.food_id = f.food_id
WHERE food_id = '1'
GROUP BY f.food_id

Although this method is different from the previous one, it produces the same result. It returns the first instance of food_menu because GROUP BY forces the query to return unique rows based on its condition.

Use GROUP_CONCAT() and Manipulate the Results in MySQL

A workaround for the problem of the previous solution is to use GROUP_CONCAT() to put all the results of food_menu in one single string, therefore it will be possible to put all their records in a single row.

What Is GROUP_CONCAT()?

GROUP_CONCAT is a function that combines the data from multiple rows to a single field. It’s a GROUP BY special function that returns a modified string if the group contains at least 1 non-null value. Otherwise, it returns NULL.

We modify the query above to GROUP_CONCAT() photos column to concatenate the results into one string.

SELECT name, price, options, GROUP_CONCAT(photo, ', ')
FROM food, food_menu
WHERE food_id = '1' 
GROUP BY food_id

By doing this, we concatenate the photo column of food_menu so that only one row per unique entry of food will be generated. The result would be as displayed:

As you can see, it concatenated the 3 photo columns that are related to Eggs in the food table.

If we remove the GROUP BY and WHERE condition.

SELECT name, price, options, GROUP_CONCAT(photo, ', ')
FROM food, food_menu

The result would look like this

Do be careful using GROUP_CONCAT(), if your string contains a comma and your CONCAT delimiter is a comma as well, parsing your column would corrupt your data.

So before you use this function, make sure that your delimiter is an invalid character for the column that you’re manipulating it with.

Related Article - MySQL Table