Join tables with two columns : Table Join « Table Join « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Table Join
3. Table Join

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