Mega Code Archive
Inner join with where clause
<![CDATA[
4>
5>
6> CREATE TABLE Products (
7> ProductID int NOT NULL ,
8> ProductName nvarchar (40) NOT NULL ,
9> SupplierID int NULL ,
10> CategoryID int NULL ,
11> QuantityPerUnit nvarchar (20) NULL ,
12> UnitPrice money NULL,
13> UnitsInStock smallint NULL,
14> UnitsOnOrder smallint NULL,
15> ReorderLevel smallint NULL,
16> Discontinued bit NOT NULL
17> )
18> GO
1> INSERT Products VALUES(1,'F',15,4,'10 - 999 g pkgs.',61.5,66,6,6,6)
2> INSERT Products VALUES(2,'M',14,4,'24 - 888 g pkgs.',34.8,74,7,7,7)
3> INSERT Products VALUES(3,'R',17,8,'24 - 777 g jars',17,171,0,5,0)
4> INSERT Products VALUES(4,'L',4,7,'5 kg pkg.',10,4,20,5,0)
5> INSERT Products VALUES(5,'R',12,1,'24 - 0.5 l bottles',1.23,445,0,25,0)
6> INSERT Products VALUES(6,'L',23,1,'500 ml',18,57,1,20,0)
7> INSERT Products VALUES(7,'O',12,2,'12 boxes',13,23,0,15,0)
8> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE TABLE Suppliers (
3> SupplierID int NOT NULL ,
4> CompanyName nvarchar (40) NOT NULL ,
5> ContactName nvarchar (30) NULL ,
6> ContactTitle nvarchar (30) NULL ,
7> Address nvarchar (60) NULL ,
8> City nvarchar (15) NULL ,
9> Region nvarchar (15) NULL ,
10> PostalCode nvarchar (10) NULL ,
11> Country nvarchar (15) NULL ,
12> Phone nvarchar (24) NULL ,
13> Fax nvarchar (24) NULL ,
14> HomePage ntext NULL
15> )
16> GO
1>
2>
3> INSERT Suppliers VALUES(1,'L','N','Manager','L 10','Van',NULL,'2800','Paris','1114108','43844115',NULL)
4> INSERT Suppliers VALUES(2,'Z','D','Manager','V 22','Zaa',NULL,'9999 ZZ','USA',' 1212','(12345) 1210',NULL)
5> INSERT Suppliers VALUES(3,'K','A','Manager','V 12','Lap',NULL,'53120','Finland',' 10956',NULL,NULL)
6> INSERT Suppliers VALUES(4,'G','W','Tester', '1 Hill','Sydney','NSW','2042','Australia','(02) 555-5914','(021) 555-2222',null)
7> INSERT Suppliers VALUES(5,'M','J','Manager','2 St.','Mon','BC','H1J 1C3','Canada','(514) 555-9022',NULL,NULL)
8> INSERT Suppliers VALUES(6,'P','G','Administrator','V 153','Sal',NULL,'84100','Italy','(089) 6547665','(089) 1111111',NULL)
9> INSERT Suppliers VALUES(7,'E','M','Sales','22 Str','Mont',NULL,'71300','France','85.57.00.07',NULL,NULL)
10> INSERT Suppliers VALUES(8,'G','E','Sales','B Ave','Ann',NULL,'74000','France','38.76.98.06','38.76.98.58',NULL)
11> INSERT Suppliers VALUES(9,'F','C','Manager','1 Str','Ste','Calgary','J2S 7S8','Canada','(514) 555-2955','(514) 555-2921',NULL)
12> 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>
2>
3>
4>
5> SELECT p.ProductID, s.SupplierID, p.ProductName, s.CompanyName
6> FROM Products p
7> INNER JOIN Suppliers s
8> ON p.SupplierID = s.SupplierID
9> WHERE p.ProductID < 4
10> GO
ProductID SupplierID ProductName CompanyName
----------- ----------- ---------------------------------------- ----------------------------------------
(0 rows affected)
1>
2> drop table Products;
3> GO
1>
2> drop table Suppliers;
3> GO
]]>