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






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








4.1.Table Join
4.1.1.SQL Server 2005 join types fall into three categories: inner, outer, and cross.
4.1.2.Selecting authors and titles using only joins.
4.1.3.A SELECT statement that joins the Bankers and Billings tables
4.1.4.Joining Tables in the WHERE Clause (not ANSI standard)
4.1.5.Using the GROUP BY Clause
4.1.6.Joining three tables.
4.1.7.Joins and Subqueries
4.1.8.Left and Right Outer Joins
4.1.9.Joining Tables in the FROM Clause (ANSI standard)
4.1.10.Table Aliasing
4.1.11.Join tables with two columns
4.1.12.The result of the previous join is then joined to another table
4.1.13.SQL-92 Three-Way Inner Joins
4.1.14.Forcing the Order of Join Processing