Mega Code Archive
Join tables with two columns
<![CDATA[
3> CREATE TABLE Orders (
4> OrderID int NOT NULL ,
5> CustomerID nchar (5) NULL ,
6> EmployeeID int NULL ,
7> OrderDate datetime NULL ,
8> RequiredDate datetime NULL ,
9> ShippedDate datetime NULL ,
10> ShipVia int NULL ,
11> Freight money NULL DEFAULT (0),
12> ShipName nvarchar (40) NULL ,
13> ShipAddress nvarchar (60) NULL ,
14> ShipCity nvarchar (15) NULL ,
15> ShipRegion nvarchar (15) NULL ,
16> ShipPostalCode nvarchar (10) NULL ,
17> ShipCountry nvarchar (15) NULL
18> )
19> GO
1>
2> SELECT CustomerID, MIN(OrderDate) AS OrderDate
3> INTO #MinOrderDates
4> FROM Orders
5> GROUP BY CustomerID
6> ORDER BY CustomerID
7> GO
(0 rows affected)
1> SELECT o.CustomerID, o.OrderID, o.OrderDate
2> FROM Orders o
3> JOIN #MinOrderDates t
4> ON o.CustomerID = t.CustomerID
5> AND o.OrderDate = t.OrderDate
6> ORDER BY o.CustomerID
7> GO
CustomerID OrderID OrderDate
---------- ----------- -----------------------
(0 rows affected)
1> DROP TABLE #MinOrderDates
2>
3> drop table orders;
4> GO
]]>