How to Eliminate Duplicate Rows When Selecting from Multiple Tables in MySQL?

SELECT from Multiple Tables and Handling Duplicates in MySQL

When selecting from multiple tables, it's common to encounter duplicate rows due to matching values in the linking column. In this scenario, you may need to eliminate these duplicates while retaining specific information.

Problem Statement:

The following query retrieves information from the "drinks" and "drinks_photos" tables:

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

However, for each drink, multiple photo entries appear, resulting in duplicate rows. The goal is to eliminate these duplicates and obtain unique rows containing the name, price, and all photos associated with each drink.

Solution:

To address this issue and maintain data integrity, we can use grouping and aggregate functions in our query.

Grouping and Aggregate Functions:

• GROUP BY: Groups rows that have the same value in the specified column(s).
• Aggregate Function: An operation (e.g., COUNT, MAX, MIN) performed on a group of rows to retrieve a single value.

In this case, we can group by the "drinks_id" column to obtain unique rows for each drink.

SQL Query:

To obtain a single row for each drink and retain all photo entries, we can use the following query:

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

• GROUP_CONCAT: Concatenates values from the specified column into a single string, separated by a specified delimiter (in this case, a comma).

Result:

This query returns the following result:

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

Explanation:

• Each drink has now only one row.
• The "all_photos" column contains a comma-separated list of all photo entries associated with the drink.

Note:

• GROUP_CONCAT is not a standardized SQL aggregate function and its behavior may vary across database systems.
• If you encounter situations where you have commas within photo filenames, you may need to consider alternative approaches, such as modifying the table structure or employing a custom function on the client side to split the concatenated string.

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