• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

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
0
minjakon
Asked:
minjakon
1 Solution
 
tigin44Commented:
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..
0
 
kerwinsiyCommented:
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

0
 
rpkhareCommented:
Check by adding:

Order By IDProduct Limit 1;
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
minjakonAuthor Commented:
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...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:
SELECT 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
GROUP BY avhd.weboggetti.IdProduct 

Open in new window

0
 
minjakonAuthor Commented:
Great, that is exactly what I was trying to do.
Thanks!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now