distinct from multiple columns with multiple 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

SQL Server 2000 Forums

Transact-SQL (2000)

distinct from multiple columns with multiple table

Author

Topic

naveenbalraj
Starting Member

18 Posts

Posted - 2010-02-05 : 11:36:01

Hi,

This is the result set:

16 D1 179 521272 19
22 D1 150 NULL 929
22 D2 151.5 1020005 936
22 D3 174.95 NULL 28
22 D1 178 NULL 31
22 D4 182 NULL 29
22 D6 190 481315 935
22 D4 195 1E770410 930
23 D5 30 C4844A 3908
24 D5 30 AC4836AN 3909
25 D5 30 C4837AN 3910
26 D5 30 C4838AN 3911
29 D7 90 544899 1454
29 D3 91.25 269-05584 1461
29 D1 96 330507 1397
29 D4 97 Email 1838
29 D6 110 321356 1462

Expected result:
16 D1 179 521272 19
22 D1 150 NULL 929
23 D5 30 C4844A 3908
24 D5 30 AC4836AN 3909
25 D5 30 C4837AN 3910
26 D5 30 C4838AN 3911
29 D7 90 544899 1454

The query i'm using

SELECT DISTINCT dbo.tblItems.cntProduct, dbo.tblVendor.VendorName, dbo.tblItemsPricing.Price,
dbo.tblItemsPricing.strVendorPartNumber, dbo.tblItemsPricing.cntQuote
FROM dbo.tblItems
INNER JOIN dbo.tblItemsPricing ON dbo.tblItemsPricing.cntProduct = dbo.tblItems.cntProduct
INNER JOIN dbo.tblVendor ON dbo.tblItemsPricing.cntVendor = dbo.tblVendor.cntVendor
ORDER BY dbo.tblItems.cntProduct, dbo.tblItemsPricing.Price

Please let me know how i can achieve the above expected result.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 11:44:16

do you have a unique valued column in table to determine the order?

naveenbalraj
Starting Member

18 Posts

Posted - 2010-02-05 : 12:11:28

yes: dbo.tblItemsPricing.cntQuote is the unique id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 12:15:41

then its easy


SELECT dbo.tblItems.cntProduct, dbo.tblVendor.VendorName, dbo.tblItemsPricing.Price, 
dbo.tblItemsPricing.strVendorPartNumber, dbo.tblItemsPricing.cntQuote
FROM dbo.tblItems 
INNER JOIN dbo.tblItemsPricing ON dbo.tblItemsPricing.cntProduct = dbo.tblItems.cntProduct
INNER JOIN (SELECT  cntProduct,MIN(cntQuote) AS Start FROM dbo.tblItemsPricing GROUP BY cntProduct) p
ON p.cntProduct=dbo.tblItems.cntProduct AND p.Start=dbo.tblItemsPricing.cntQuote
INNER JOIN dbo.tblVendor ON dbo.tblItemsPricing.cntVendor = dbo.tblVendor.cntVendor
ORDER BY dbo.tblItems.cntProduct, dbo.tblItemsPricing.Price

naveenbalraj
Starting Member

18 Posts

Posted - 2010-02-05 : 12:27:28

Thanks for the quick reply. But, sorry I need to have the price as well minimum.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 12:32:58

[code]SELECT dbo.tblItems.cntProduct, dbo.tblVendor.VendorName, dbo.tblItemsPricing.Price,
dbo.tblItemsPricing.strVendorPartNumber, dbo.tblItemsPricing.cntQuote
FROM dbo.tblItems
INNER JOIN dbo.tblItemsPricing ON dbo.tblItemsPricing.cntProduct = dbo.tblItems.cntProduct
INNER JOIN (SELECT cntProduct,MIN(Price) AS Start FROM dbo.tblItemsPricing GROUP BY cntProduct) p
ON p.cntProduct=dbo.tblItems.cntProduct AND p.Start=dbo.tblItemsPricing.Price
INNER JOIN dbo.tblVendor ON dbo.tblItemsPricing.cntVendor = dbo.tblVendor.cntVendor
ORDER BY dbo.tblItems.cntProduct, dbo.tblItemsPricing.Price
[/code]

naveenbalraj
Starting Member

18 Posts

Posted - 2010-02-05 : 12:40:40

Thanx a lot. You are really great. Mimimal input maximum benefit.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 12:41:43

welcome

naveenbalraj
Starting Member

18 Posts

Posted - 2010-02-05 : 13:33:06

Hi,

when i ran the query on my whole db. I found certain records are repetitive, meaning I see more than one cntProduct, which is suppose to be unique for the returned result set.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 13:36:35

quote:
Originally posted by naveenbalraj

Hi,

when i ran the query on my whole db. I found certain records are repetitive, meaning I see more than one cntProduct, which is suppose to be unique for the returned result set.

can be either because you've two records with same min price in tblItemsPricing or multiple records in tblVendor for same product

naveenbalraj
Starting Member

18 Posts

Posted - 2010-02-05 : 14:21:20

yes, the price is the same. i was to mention in my prev reply.
How can the query be modified to pick any one?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-06 : 06:23:03

[code]SELECT dbo.tblItems.cntProduct, dbo.tblVendor.VendorName, dbo.tblItemsPricing.Price,
dbo.tblItemsPricing.strVendorPartNumber, dbo.tblItemsPricing.cntQuote
FROM dbo.tblItems
INNER JOIN dbo.tblItemsPricing ON dbo.tblItemsPricing.cntProduct = dbo.tblItems.cntProduct
INNER JOIN (SELECT cntProduct,MIN(Price) AS Start FROM dbo.tblItemsPricing GROUP BY cntProduct) p
ON p.cntProduct=dbo.tblItems.cntProduct AND p.Start=dbo.tblItemsPricing.Price
INNER JOIN (SELECT cntProduct,Price,MIN(cntQuote) AS Start AS Start FROM dbo.tblItemsPricing GROUP BY cntProduct,Price) p1
ON p1.cntProduct=dbo.tblItems.cntProduct AND p1.Price=dbo.tblItemsPricing.Price AND p1.Start = dbo.tblItemsPricing.cntQuote
INNER JOIN dbo.tblVendor ON dbo.tblItemsPricing.cntVendor = dbo.tblVendor.cntVendor
ORDER BY dbo.tblItems.cntProduct, dbo.tblItemsPricing.Price
[/code]

naveenbalraj
Starting Member

18 Posts

Posted - 2010-02-06 : 11:39:33

Thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-06 : 11:41:37

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources