How Can I Avoid Duplicate Rows When Joining Multiple Tables in SQL SELECT Statements?

Avoiding Duplicate Results in Multi-Table SELECT Statements: A Comprehensive Guide

When performing SELECT statements that join data from multiple tables, you may encounter duplicate rows, as exemplified in the scenario provided:

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

This query retrieves 5 rows for drinks, with photo as the only unique field. However, name and price are repeated, leading to duplicate rows for drinks like "fanta."

The Solution: Grouping and Aggregate Functions

To eliminate these duplicates, you need to utilize grouping and aggregate functions. Grouping allows you to combine rows based on common values, while aggregate functions enable you to extract a specific value for each group.

Grouping by Drinks

If you want a single row for each drink, regardless of the number of photos associated with it, you can group by the drinks_id:

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

This query will return:

name	price	photo
fanta	5	./images/fanta-1.jpg
dew	4	./images/dew-1.jpg

Concatenating Photos with GROUP_CONCAT

If you prefer to have an array of photos for each drink, MySQL provides the GROUP_CONCAT function, which concatenates values from the same group:

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

This query will yield:

name	price	photo
fanta	5	./images/fanta-1.jpg,./images/fanta-2.jpg,./images/fanta-3.jpg
dew	4	./images/dew-1.jpg,./images/dew-2.jpg

Considerations

Note that GROUP_CONCAT should be used with caution, as it can lead to database performance issues or return excessively large strings if the concatenated values contain commas.

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