SELECT * FROM multiple tables. MySQL

Question

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

Calycle

Well you've got 5 matching rows, as you've got several rows with the same drinks_id. What would you expect to happen? Which photo would you expect to keep? – Hesper

Answer 1

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.

Thrown

Yea i wanted name, price and an array of images for each drink. – Calycle

In which language do you code your program doing those queries? Because MySQL doesn't support arrays as field types. You have to do some string tricks like with the GROUP_CONCAT and split it again. One normally doesn't do this in relational databases. You should do the grouping in the program, not in SQL (detect the duplicate IDs and build those arrays in your program). – Thrown

@Calycle Please also have a look at #9024748 – Thrown

@Calycle If you want all images, it will be better (faster) not to use GROUP BY and GROUP_CONTACT, but continue as you did but order by drinks.id. That way you'll be able to loop through resultset and collect images into arrays on client side. – Lymphadenitis

It equivalent to INNER JOIN. – Travax

It should also be noted that GROUP_CONCAT is limited to 1024 characters by default. See: sysvar_group_concat_max_len and MySQL and GROUP_CONCAT() maximum length – Carin

Answer 2

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 ?

Totemism

Answer 3

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

Lymphadenitis

You should better group by the id. Always group by your unique field to get distinct rows of a source table. In your case, if two drinks have the same name (but are different rows in the table) you'd group them together, too. – Thrown

thank you leemes, I didn't noticed that there is unique id field. I've edited my answer. Of course using id its better. @ypercube, do you really think, that your comment adds something usual to post? – Lymphadenitis

@bhovhannes: No, you are right, sorry. Snarky comments do not add anything useful. Corrected answer is better - although I still disagree with this use of GROUP BY (that is not SQL-standard) without an explanation of why it works and how it will return a semi-random photo for every drink. (The "first" in the "you'll get the first photo" phrase is just wrong). – Edd

@ypercube, yes, using GROUP BY for filtering rows (such as in this situation) is not SQL standard. But that definitely works for MySQL, is fast, and it returns photo with lowest id. I've never seen another behavior in MySQL (though it is not documented). I've added a note to my post – Lymphadenitis