How to Avoid Duplicate Rows When Joining Multiple Tables in MySQL?

JOIN with Multiple Tables in MySQL

Problem:
When selecting data from multiple tables in MySQL using a simple JOIN without specifying any conditions, it can result in duplicate rows due to multiple rows being joined for each matching key. How can we eliminate these duplicates and retrieve unique rows for each drink, including all associated photos?

Solution:
To resolve this issue, we need to utilize grouping and aggregate functions to combine duplicate rows and select a desired value from them.

Firstly, we group the rows by the drinks_id to obtain only one row for each drink:

SELECT name, price, photo
FROM drinks, drinks_photos
WHERE drinks.id = drinks_id 
GROUP BY drinks_id

This approach will return one row per drink with only one photo. To include all the associated photos, we can use the GROUP_CONCAT function to concatenate the photo file names into a single string:

SELECT name, price, GROUP_CONCAT(photo, ',')
FROM drinks, drinks_photos
WHERE drinks.id = drinks_id 
GROUP BY drinks_id

This query will generate a table with unique drink rows and a concatenated string containing all the photo file names for each drink.

Note that GROUP_CONCAT is a MySQL-specific function and may not be supported in other database systems. In such cases, alternative approaches, such as subqueries or array data types, may need to be considered.

The above is the detailed content of How to Avoid Duplicate Rows When Joining Multiple Tables in MySQL?. For more information, please follow other related articles on the PHP Chinese website!