April 1, 2009 at 9:34 pm
hello frnds,
i need to get distinct values from three table namely Products,ProdFiles,ProdCategories. Here CategoryId is common column of three tables, i have used the below query but which returns every value in two or more times..how to correct this...
My Query is:
SELECT p.productid,p.name,p.categoryid,p.groupid,pf.fileid,pf.filename,pf.productid,pf.categoryid,pc.categoryid,pc.categoryname FROM Products p JOIN ProductFiles pf ON pf.CategoryID=p.CategoryID JOIN ProductCategories pc ON pc.CategoryID=p.CategoryID
My Table Structure is:
Table Products
ProductID-int NOT NULL
Name -nvarchar(50)
CategoryID-int
GroupID -int
ProdIDNameCatIDGrpID
1Sam001110
2KB001211
6Sam002112
7LG002212
Table ProductFiles
FileID -int NOT NULL
Filename -nvarchar(50)
ProductID-int
CategoryID-int
FileIDFileNameprodIDCatID
145.JPG11
215.JPG22
36.JPG 61
42.BMP72
Table ProductCategories
CategoryID-int NOT NULL
CatName-nvarchar(50)
CatIDCatName
1Samsung
2LG
3Philips
plz anyone knows solution inform me....thnks..
April 2, 2009 at 2:35 am
Are you sure that the Products & ProdFiles tables linked with CategoryID instead of ProductID? I suspect they aren't.
If they are indeed, can you provide the output required for the sample data you provided?
--Ramesh
April 2, 2009 at 3:36 am
you can use SELECT DISTINCT ....[columns]...FROM YOURTABLE to eliminate duplicates.
if the whole row of data is not unique, then you'll have to use GROUP BY , putting columns you don't want duplicated in the group by section of the query.
SELECT
p.productid,
p.name,
p.categoryid,
p.groupid,
pf.fileid,
pf.filename,
pf.productid,
pf.categoryid,
pc.categoryid,
pc.categoryname
FROM Products p
JOIN ProductFiles pf ON pf.CategoryID=p.CategoryID
JOIN ProductCategories pc ON pc.CategoryID=p.CategoryID
Group by
p.productid,
p.name,
p.categoryid,
p.groupid,
pf.fileid,
pf.filename,
pf.productid,
pf.categoryid,
pc.categoryid,
pc.categoryname
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply