Want to learn methods of querying MySQL select from multiple tables? You are at the right place. This tutorial will teach you how to perform this task. Let’s check it out and follow along.

Here is an example.

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

Now envision example tables for every entry FROM.

• food:

• food_menu:

According to the above tables, Eggs have three photos, Ice cream has three, and Ramen has two. The result that we want to create is a combined table of food_menu and food, displaying all the food at once and pairing it with the relevant menu items’ photographs.

Suppose you query this:

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

Here is the output:

There are numerous rows in the food_menu linked to food, resulting in data duplication. In this instance, food_menu contains three photos specifically related to Eggs.

As they are all regarded as different entities connected to the food table, a query can’t merge all the elements in food_menu into one row.

Suppose you wish to query food_menu and food simultaneously in one single row; below are some approaches.

Mysql select from multiple tables

Method 1: MySQL Select From Multiple Tables Utilizing Group By Food

In this method, the two tables are combined into a single result using GROUP BY. However, since you are mandating unique results, you will only receive the food_menu initial instance.

The following is the query of the food table’s GROUP BY.

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

Output:

Despite the query returning just one photo, which is the food_menu initial instance discovered, you have now met the criteria.

Method 2: MySQL Select From Multiple Tables Utilizing JOIN

This method uses the command RIGHT JOIN or JOIN in SQL. You can join the food_menu using its foreign key food_id in place of the script’s two FROM conditions. As you can see below, f stands for food, and fm stands 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

Even though this approach differs from the prior one, this one yields the same outcome. Since GROUP BY causes the query to give back distinct rows according to its criteria, it gives back the food_menu initial instance.

Method 3: Utilize MySQL GROUP_CONCAT() Function And Manipulate The Outcomes

Another method is to utilize GROUP_CONCAT() to combine all of the food_menu results into a single string, allowing you to arrange all their entries into one row.

What Is The GROUP_CONCAT() Function?

With the help of GROUP_CONCAT(), numerous rows of data are combined into one single field. If there is at least one non-null value present in the group, this GROUP BY’s particular function produces a changed string. If not, NULL is returned.

To combine the output into one single string, you can change the above query to the GROUP_CONCAT() pictures column.

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

This concatenates the food_menu photo column, so just one row is generated for each distinct food entry.

Output:

Here, it combined the three photo columns in the table food that are relevant to Eggs.

Suppose the WHERE and GROUP BY conditions are removed:

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

Output:

Be cautious while utilizing the GROUP_CONCAT() function. The data might be corrupted during column parsing if commas appear in your string and the delimiter CONCAT is also a comma.

Consequently, ensure that the delimiter is an incorrect character for the columns you are modifying before utilizing this function.

The Bottom Line

Now you know how to perform querying MySQL select from multiple tables after reading this post. Keep practicing these approaches, and you will soon master them.

Bonus: If you want to gain more MySQL skills, start with this guide on creating a MySQL query with not equal operators.