Link to home
Start Free TrialLog in
Avatar of minjakon
minjakonFlag for Italy

asked on

Use of SELECT DISTINCT

Hi
I need to create JOIN of 3 tables. I use this scheme:

Table1.ID1is related to Table2.ID1
Table2.ID2 is related to Table3.ID2

avhd.weboggetti.IdProduct is related to avhd.fotolinks.IdArticolo
avhd.fotolinks.IdFoto is related to avhd.foto.ID

Then I need to filter result by avhd.weboggetti.IdProduct to see only one record (without duplicates). I'm trying to use SELECT DISTINCT, but it still visualize duplicate values.

How can I do this?
SELECT DISTINCT IdProduct,CustomT1Desc, CustomT2Desc, UM, Description,CustomT9Desc,IdArticoloMasterTagliaColore,
DescriptionExt1,PrezzoListinoUfficiale,ProdCode,QTotMagazzino,ProductMainCategoryAndFathers,IdFoto,Percorso,NomeFile
 
FROM avhd.weboggetti
 
LEFT JOIN (avhd.fotolinks LEFT JOIN avhd.foto ON avhd.fotolinks.IdFoto=avhd.foto.ID)
 
ON avhd.weboggetti.IdProduct=avhd.fotolinks.IdArticolo

Open in new window

DuplicateValues.png
Avatar of tigin44
tigin44
Flag of Türkiye image

try this

SELECT DISTINCT IdProduct,CustomT1Desc, CustomT2Desc, UM, Description,CustomT9Desc,IdArticoloMasterTagliaColore,
DescriptionExt1,PrezzoListinoUfficiale,ProdCode,QTotMagazzino,ProductMainCategoryAndFathers,IdFoto,Percorso,NomeFile
 
FROM avhd.weboggetti
      LEFT JOIN avhd.fotolinks ON avhd.weboggetti.IdProduct= avhd.fotolinks.IdArticolo
      LEFT JOIN avhd.foto ON avhd.fotolinks.IdFoto=avhd.foto.ID

but if your detail tables including more then 1 row per master relation thane this query results many rows..
hi did you check all the return fields, meaning all the columns requested, there could probably some item that makes them unique why distinct doesnt work properly

Check by adding:

Order By IDProduct Limit 1;
Avatar of minjakon

ASKER

Thanks to everybody, but It still gives me a duplicate values.
tigin44: In effect, my query returns me duplicate values, as same as yours.

kerwinsiy: I joine 3 tables and there is no fields with uniquw value

rpkhare: It renurns me only one row...
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Great, that is exactly what I was trying to do.
Thanks!