How to Handle Duplicate Rows When Using SELECT DISTINCT with Multiple Tables in MySQL?

SELECT DISTINCT with Multiple Tables in MySQL

In the MySQL query, you're using an implicit join between the drinks and drinks_photos tables without any conditions in the WHERE clause. This results in a Cartesian product, yielding five rows with repeated values for name and price. To eliminate these duplicates, you can use the SELECT DISTINCT clause.

However, if you want to retain all the photos for each drink, you can't simply use SELECT DISTINCT because it returns only distinct rows. Instead, you need to use a combination of grouping and aggregate functions.

Grouping and Aggregate Functions

Grouping aggregates rows based on a specified column or columns. In this case, you want to group the results by the drinks_id to get one row per drink. You can then use an aggregate function to select the desired value for each grouped result.

For example, to retrieve the first photo for each drink, you can use:

SELECT name, price, MIN(photo) AS photo
FROM drinks
JOIN drinks_photos ON drinks.id = drinks_id
GROUP BY drinks_id;

The MIN() aggregate function returns the minimum value of the photo column for each group.

GROUP_CONCAT

Alternatively, if you want to concatenate all the photos for each drink into a single string, you can use the GROUP_CONCAT() function:

SELECT name, price, GROUP_CONCAT(photo, ',') AS photos
FROM drinks
JOIN drinks_photos ON drinks.id = drinks_id
GROUP BY drinks_id;

However, note that GROUP_CONCAT() is not a standard SQL aggregate function and its use can vary across different databases.

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