How can i avoid duplicate key in joining tables in a one-many relationship?

Question

I have 6 tables that i am trying to query but i am getting duplicate primary keys in the query result. Here are the tables :

userarticles

id nomarticles 
 1 escarpin_estelle
 2 Tallon_grace

articlesize

productid  idsizes
 1          1
 1          2
 2          3
 2          5

sizes

idsizes size
  1       36
  2       38
  3       40
  4       44
  5       32

articlematieres

id_article id_matiere
  1          2
  1          1
  2          3
  2          3

matiere

matiereid  matierename
 1          daim
 2          coton
 3          polyster

article_imgs

imageid articleID filenames
  1       1       rouge2017-10-03.jpg   
  2       1       2017-10-03-204220.jpg
  3       2       moulante201.jpg
  4       2       avec-decollete.jpg

The desire result should be to have

id  articlename        size   filenames                            matiere 
 1  escarpin_estelle   38,45  rouge2017-10-03.jpg,moulante201.jpg  coton,daim

Here ismy query :

SELECT id,nomarticle,filenames,size,matierename FROM userarticles AS products LEFT JOIN article_imgs AS images ON images.articleID = products.id LEFT JOIN articlesize AS prodt ON prodt.idarticle = products.id LEFT JOIN sizes AS s ON s.idsizes = prodt.idsize LEFT JOIN articlematieres AS prodmat ON prodmat.id_article = products.id LEFT JOIN matiere AS m ON m.matiereid = prodmat.id_matiere WHERE products.id = 1

Have a look at [this answer](https://stackoverflow.com/a/276949/2869344) to see if it helps. I think you are looking for the `GROUP_CONCAT()` function, but I'm not experienced with MySQL, sorry. — pcdev, Oct 04 '17 at 00:57
There is no sample data as per desire output. Could correct your sample data and expected output ? — Ravi, Oct 04 '17 at 03:38

Jacques Amar · Answer 1 · 2017-10-04T04:17:16.343

0

You really do have multiple answers:

products.id = 1 has 2 images, 2 sizes and 2 matiere

You really can't get a unique answer unless you specify more fields in your WHERE

** --- OPTIONALLY --- **

You can add LIMIT 1 to your query to get the first one, but it's probably wrong.

edited Oct 04 '17 at 04:17

answered Oct 04 '17 at 03:36

Jacques Amar

1,803
1
10
12

How can i avoid duplicate key in joining tables in a one-many relationship?

1 Answers1