SELECT * FROM multiple tables. MySQL

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

yeilds 5 rows (5 arrays), photo is the only unique field in a row. name, price get repeated (here, fanta- name, price repeat 3 times.) How do i get rid of these duplicates?

Edit: I want name, price and all photo for each drink.

 id      name      price
1.    fanta        5
2.     dew         4




id      photo                   drinks_id
1.     ./images/fanta-1.jpg      1
2.     ./images/fanta-2.jpg      1
3.     ./images/fanta-3.jpg      1
4.     ./images/dew-1.jpg        2
5.     ./images/dew-2.jpg        2
711058 次浏览
小开
最佳答案

What you do here is called a JOIN (although you do it implicitly because you select from multiple tables). This means, if you didn't put any conditions in your WHERE clause, you had all combinations of those tables. Only with your condition you restrict your join to those rows where the drink id matches.

But there are still X multiple rows in the result for every drink, if there are X photos with this particular drinks_id. Your statement doesn't restrict which photo(s) you want to have!

If you only want one row per drink, you have to tell SQL what you want to do if there are multiple rows with a particular drinks_id. For this you need grouping and an aggregate function. You tell SQL which entries you want to group together (for example all equal drinks_ids) and in the SELECT, you have to tell which of the distinct entries for each grouped result row should be taken. For numbers, this can be average, minimum, maximum (to name some).

In your case, I can't see the sense to query the photos for drinks if you only want one row. You probably thought you could have an array of photos in your result for each drink, but SQL can't do this. If you only want any photo and you don't care which you'll get, just group by the drinks_id (in order to get only one row per drink):

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


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

In MySQL, we also have GROUP_CONCAT, if you want the file names to be concatenated to one single string: 

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


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

However, this can get dangerous if you have , within the field values, since most likely you want to split this again on the client side. It is also not a standard SQL aggregate function.

小开

In order to get rid of duplicates, you can group by drinks.id. But that way you'll get only one photo for each drinks.id (which photo you'll get depends on database internal implementation).

Though it is not documented, in case of MySQL, you'll get the photo with lowest id (in my experience I've never seen other behavior).

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

You will have the duplicate values for name and price here. And ids are duplicate in the drinks_photos table.There is no way you can avoid them.Also what exactly you want the output ?


提交答案