Mega Code Archive
Join and sub query
<![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>
3> CREATE TABLE OrderDetails (
4> OrderID int NOT NULL ,
5> ProductID int NOT NULL ,
6> UnitPrice money NOT NULL DEFAULT (0),
7> Quantity smallint NOT NULL DEFAULT (1),
8> Discount real NOT NULL DEFAULT (0)
9> )
10> GO
1> INSERT OrderDetails VALUES(10248,11,14,12,0)
2> INSERT OrderDetails VALUES(10248,42,9.8,10,0)
3> INSERT OrderDetails VALUES(10248,72,34.8,5,0)
4> INSERT OrderDetails VALUES(10249,14,18.6,9,0)
5> INSERT OrderDetails VALUES(10249,51,42.4,40,0)
6> INSERT OrderDetails VALUES(10250,41,7.7,10,0)
7> INSERT OrderDetails VALUES(10250,51,42.4,35,0.15)
8> INSERT OrderDetails VALUES(10250,65,16.8,15,0.15)
9> INSERT OrderDetails VALUES(10251,22,16.8,6,0.05)
10> INSERT OrderDetails VALUES(10251,57,15.6,15,0.05)
11> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> DECLARE @FirstDate smalldatetime
3>
4> SELECT @FirstDate = MIN(OrderDate) FROM Orders
5>
6> SELECT DISTINCT o.OrderDate, od.ProductID
7> FROM Orders o
8> JOIN OrderDetails od
9> ON o.OrderID = od.OrderID
10> WHERE o.OrderDate = @FirstDate
11> GO
OrderDate ProductID
----------------------- -----------
(0 rows affected)
1>
2> SELECT DISTINCT o.OrderDate, od.ProductID
3> FROM Orders o
4> JOIN OrderDetails od
5> ON o.OrderID = od.OrderID
6> WHERE o.OrderDate = (SELECT MIN(OrderDate) FROM Orders)
7> GO
OrderDate ProductID
----------------------- -----------
(0 rows affected)
1>
2> drop table orders;
3> drop table OrderDetails;
4> GO
1>
]]>