How to get Distinct Values from more than 1 table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get Distinct Values from more than 1 table

Author

Topic

sadaiyan
Starting Member

6 Posts

Posted - 2009-04-01 : 09:25:15

hello frnds,
i need to get distinct values from three table namely Products,ProdFiles,ProdCategories. In that CategoryId is common column, i have used the below query but which returns duplicate values as well..how to correct this...
My Query is:
SELECT * FROM Products,ProductFiles,ProductCategories WHERE Products.CategoryID=ProductFiles.CategoryID AND Products.CategoryID IN(SELECT CategoryID FROM ProductCategories)

My Table Structure is:

Table Products

ProductID-int NOT NULL
Name -nvarchar(50)
CategoryID-int
GroupID -int

Table ProductFiles

FileID -int NOT NULL
Filename -nvarchar(50)
ProductID-int
CategoryID-int

Table ProductCategories

CategoryID-int NOT NULL
CatName-nvarchar(50)

plz anyone knows solution inform me....thnks..

sadaiyan.l

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-01 : 09:34:34

Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-04-01 : 09:47:35

Is this what you mean? Or you'll need to do as madhivanan asks.

SELECT distinct * FROM Products,ProductFiles,ProductCategories WHERE Products.CategoryID=ProductFiles.CategoryID AND Products.CategoryID IN(SELECT CategoryID FROM ProductCategories)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-01 : 10:05:32

quote:
Originally posted by darkdusky

Is this what you mean? Or you'll need to do as madhivanan asks.

SELECT distinct * FROM Products,ProductFiles,ProductCategories WHERE Products.CategoryID=ProductFiles.CategoryID AND Products.CategoryID IN(SELECT CategoryID FROM ProductCategories)

I dont think it will work as * will include all columns from three tables

Madhivanan

Failing to plan is Planning to fail

sadaiyan
Starting Member

6 Posts

Posted - 2009-04-01 : 10:10:32

quote:
Originally posted by madhivanan

Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

Table:Products

ProdID Name CatID GrpID
1 Sam001 1 10
2 KB001 2 11
6 Sam002 1 12
7 LG002 2 12

Table:ProductFile

FileID FileName prodID CatID
1 45.JPG 1 1
2 15.JPG 2 2
3 6.JPG 6 1
4 2.BMP 7 2

Table:ProdCategories

CatID CatName
1 Samsung
2 LG
3 Philips

I have given my three tables data,now i want to get data from all tables which has same CategoryID...this is my need..i used above query but which returns repeated values from all tables...need to get DISTINCT values..

sadaiyan.l

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-01 : 10:26:05

Follow the first link in my signature, and it will tell you what we need.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-01 : 12:38:18

quote:
Originally posted by sadaiyan

quote:
Originally posted by madhivanan

Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

Table:Products

ProdID Name CatID GrpID
1 Sam001 1 10
2 KB001 2 11
6 Sam002 1 12
7 LG002 2 12

Table:ProductFile

FileID FileName prodID CatID
1 45.JPG 1 1
2 15.JPG 2 2
3 6.JPG 6 1
4 2.BMP 7 2

Table:ProdCategories

CatID CatName
1 Samsung
2 LG
3 Philips

I have given my three tables data,now i want to get data from all tables which has same CategoryID...this is my need..i used above query but which returns repeated values from all tables...need to get DISTINCT values..

sadaiyan.l

something like


SELECT columns...
FROM Products p
JOIN ProductFile pf
ON pf.CatID=p.CatID
JOIN ProdCategories pc
ON pc.CatID=p.CatID

and if this is not waht you want show the output you expect out of above data

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources