Distinct Join - SQL Server Forums

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

Distinct Join

Author

Topic

xsys
Starting Member

5 Posts

Posted - 2008-07-21 : 10:29:19

Greetings!

I am sure this is simple for some of you, but I just can't see it. The semantics of SQL drives me nuts

All I need to do is create a DISTINCT result from a query of three tables. The example I have is for the Northwind DB:


SELECT [Order Details].OrderID AS ID,
   [Order Details].Quantity AS Quantity,
   P.ProductName AS Product,
   Orders.ShipCountry AS Country
FROM [Order Details] JOIN Orders
  ON [Order Details].OrderID = Orders.OrderID
 JOIN (SELECT DISTINCT ProductID, ProductName FROM Products) P
  ON [Order Details].ProductID = P.ProductID
GROUP BY P.ProductName,
	Orders.ShipCountry,
	[Order Details].Quantity,
	[Order Details].OrderID
ORDER BY Product;

I am trying to return distinct product names, but I am getting multiple occurrences. If someone would kindly show me how to do this, you will be answering many questions for me in regards to JOIN and sub-queries in general.

Thank you!

JR

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-21 : 10:32:08

do you want to SUM() the Quantity ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 10:32:59

Can you post some sample data from your tables and also output you expect out of them. Obviuosly when you want distinct product names, you can return only one set of values for other fields. What's your rule for determing their value? i.e do you want max value. min value or random value? please show by means of some sample data what you're expecting.

xsys
Starting Member

5 Posts

Posted - 2008-07-21 : 13:09:02

Thanks for the quick reply!

Here are the first 20 rows from the result of running this query on the Northwind DB:


ID     Qty      Product Name    Shipped To
---------------------------------------
10415	2	Alice Mutton	USA
10852	6	Alice Mutton	USA
10949	6	Alice Mutton	Canada
10972	6	Alice Mutton	France
10319	8	Alice Mutton	Mexico
10550	8	Alice Mutton	Spain
10698	8	Alice Mutton	Austria
10444	10	Alice Mutton	Sweden
10915	10	Alice Mutton	Mexico
10911	12	Alice Mutton	Spain
11059	12	Alice Mutton	Brazil
10279	15	Alice Mutton	Germany
10294	15	Alice Mutton	USA
10890	15	Alice Mutton	France
10922	15	Alice Mutton	Brazil
10564	16	Alice Mutton	USA
10871	16	Alice Mutton	France
10573	18	Alice Mutton	Mexico
10338	20	Alice Mutton	USA
10696	20	Alice Mutton	USA

As you can see, there are multiple results for "Alice Mutton". I want to limit the results to only one result per product name:


ID     Qty      Product Name    Shipped To
---------------------------------------
10415	2	Alice Mutton	        USA
11077	4	Aniseed Syrup	        USA
10807	1	Boston Crab Meat	Italy
10963	2	Camembert Pierrot	Portugal
10644	4	Carnarvon Tigers	Brazil
11005	2	Chai	                Finland
11030	100	Chang	                USA
10784	2	Chartreuse verte	Italy
10764	130	Chartreuse verte	Austria
11077	1	Chef Anton's Cajun Seasoning	USA
10708	4	Chef Anton's Gumbo Mix	USA
10604	6	Chocolade	Portugal
10828	2	Côte de Blaye	Argentina
10636	6	Escargots de Bourgogne	Finland
11038	2	Filo Mix	        Belgium
10304	2	Flotemysost	        Mexico
10850	4	Geitost	                France
10382	60	Geitost                 Austria

Maybe it would be better to constrain this by ProductName per ShipCountry?

Thank you!

JR

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 13:39:05

[code]SELECT t.*
FROM YourTable t
INNER JOIN (SELECT MIN(ID) AS MinID,[Product Name]
FROM YourTable
GROUP BY [Product Name])t1
ON t1.MinID=t.ID
AND t1.[Product Name]=t.[Product Name][/code]

xsys
Starting Member

5 Posts

Posted - 2008-07-21 : 14:38:59

quote:

Originally posted by visakh16

SELECT t.*
FROM YourTable t
INNER JOIN (SELECT MIN(ID) AS MinID,[Product Name]
            FROM YourTable
            GROUP BY [Product Name])t1
ON t1.MinID=t.ID
AND t1.[Product Name]=t.[Product Name]

I 'filled in the blanks' to get this:


SELECT t.*
FROM [Order Details] t
INNER JOIN (SELECT MIN(ProductID) AS MinID, ProductName
            FROM Products
            GROUP BY ProductName) t1
ON t1.MinID = t.ProductID
AND t1.ProductName = t.ProductName

But there are several problems:

There is no ProductName column in [Order Details], so the last line (the AND line) will not work, although if you comment it out, you get all of the rows correctly.

I need to get data from three separate tables (columns in parentheses):

Order Details (OrderID, ProductID, Quantity)
Orders (OrderID, ShipCountry)
Products (ProductID, ProductName)

So there is some juggling to do to get the ProductName, do you see? The final result should be four columns:

OrderID
Quantity
ProductName
ShipCountry

Thank you for taking the time to look at this!

JR

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 14:48:37

[code]SELECT od.OrderID,
od.Quantity,
p.ProductName,
o.ShipCountry
FROM [Order Details] od
INNER JOIN (SELECT OrderID, MIN(ProductID) AS MinProductID
FROM [Order Details]
GROUP BY OrderID)t
ON t.OrderID=od.OrderID
AND t.MinProductID=od.ProductID
INNER JOIN Orders o
ON o.OrderID=t.OrderID
INNER JOIN Products p
ON p.ProductID=t.ProductID[/code]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-21 : 14:50:00

http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/60261.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

xsys
Starting Member

5 Posts

Posted - 2008-07-21 : 16:23:54

quote:

Originally posted by visakh16

SELECT od.OrderID,
od.Quantity,
p.ProductName,
o.ShipCountry
FROM [Order Details] od
INNER JOIN (SELECT OrderID, MIN(ProductID) AS MinProductID 
      FROM [Order Details] 
      GROUP BY OrderID)t
ON t.OrderID=od.OrderID
AND t.MinProductID=od.ProductID
INNER JOIN Orders o
ON o.OrderID=t.OrderID
INNER JOIN Products p
ON p.ProductID=t.ProductID

That is very nice!

(Last line needs to be ON p.ProductID=t.MinProductID)

So, this limits things by the order id, which is really about the best you can do. I think the request to have things limited by ProductName was a little strange anyway.

I appreciate your time

xsys
Starting Member

5 Posts

Posted - 2008-07-21 : 16:25:21

quote:
Originally posted by jsmith8858

http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/60261.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

I thoroughly enjoyed those

Thanks!

JR

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources