JIYIK CN >

This article explains how to use MySQL to query from multiple tables in one script SELECT.

Let's demonstrate a situation:

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

Now, let's imagine FROMan example table with for each entry.

• food

• food_menu

According to the table above, we can see that eggs have 3 photos, ice cream also has 3 photos, and ramen has 2 photos. What we want to output is an aggregate table of foodand food_menu, showing all the food together and combining 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 will be something like this:

The data is duplicated because food_menuthere are multiple rows of data in foodthat are related to . In this case, food_menu3 of the photos in are directly tied to Eggs.

It is not possible for a simple query to food_menujoin all the entries in into a single record, because they are all considered foodindependent entities related to the table.

foodIf you want to query both and in one line food_menu, then there are a few options.

foodHow to SELECT from multiple tables using GROUP BY

This approach uses GROUP BYto aggregate the two tables into one result. But the downside is that you only get food_menuthe first instance of since we force the result to be unique.

The following is GROUP BY fooda query on the table.

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

It will display the following result.

Now our condition is satisfied, although only one photo is returned, which is the first food_menuinstance found by the query.

JOINUsing from multiple tables in MySQLSELECT

This method utilizes the SQL JOINor RIGHT JOINcommand.

We don't have 2 conditions in the script , but instead join FROMbased on its foreign key . We will use as an alias for and as an alias for .food_idfood_menuffoodfmfood_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 food_menuthe first instance of because GROUP BY forces the query to return only one row based on its condition.

Use in MySQL GROUP_CONCAT()and process the results

A workaround to the previous solution is to use to GROUP_CONCAT()put food_menuall the results of into a single string, thus putting all their records on a single row.

What isGROUP_CONCAT()

GROUP_CONCATis a function that merges multiple rows of data into one field. It is a GROUP BYspecial function of that returns a modified string if the group contains at least 1 non-null value. Otherwise, it returns NULL.

We modify the above query to GROUP_CONCAT()a photo column and concatenate the results into a string.

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

By doing this, we are food_menuconcatenating the photo columns of so that each foodunique entry of will produce only one record. The result will be as shown below.

foodAs you can see, it joins the columns from the three Eggs-related tables photo.

If we remove GROUP BYthe and conditionsWHERE

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

The result is this

Be careful when using GROUP_CONCAT(), if your string contains a comma and your CONCATdelimiter is also a comma, your data will be corrupted when parsing your column.

So before using this function, make sure your delimiter is an invalid character for the column you are operating on.