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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] SELECT * FROM multiple tables. MySQL

Examples related to mysql

Examples related to multiple-tables