Mega Code Archive
Inner join two table with column in common
<![CDATA[
5> CREATE TABLE Suppliers (
6> SupplierID int NOT NULL ,
7> CompanyName nvarchar (40) NOT NULL ,
8> ContactName nvarchar (30) NULL ,
9> ContactTitle nvarchar (30) NULL ,
10> Address nvarchar (60) NULL ,
11> City nvarchar (15) NULL ,
12> Region nvarchar (15) NULL ,
13> PostalCode nvarchar (10) NULL ,
14> Country nvarchar (15) NULL ,
15> Phone nvarchar (24) NULL ,
16> Fax nvarchar (24) NULL ,
17> HomePage ntext NULL
18> )
19> 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> CREATE TABLE Products (
4> ProductID int NOT NULL ,
5> ProductName nvarchar (40) NOT NULL ,
6> SupplierID int NULL ,
7> CategoryID int NULL ,
8> QuantityPerUnit nvarchar (20) NULL ,
9> UnitPrice money NULL,
10> UnitsInStock smallint NULL,
11> UnitsOnOrder smallint NULL,
12> ReorderLevel smallint NULL,
13> Discontinued bit NOT NULL
14> )
15> 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> SELECT *
3> FROM Products
4> INNER JOIN Suppliers
5> ON Products.SupplierID = Suppliers.SupplierID
6> GO
ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued SupplierID CompanyName
ContactName ContactTitle Address City Region PostalCode Country
Phone Fax HomePage
----------- ---------------------------------------- ----------- ----------- -------------------- --------------------- ------------ ------------ ------------ ------------ ----------- ----------------
------------------------ ------------------------------ ------------------------------ ------------------------------------------------------------ --------------- --------------- ---------- ---------
------ ------------------------ ------------------------ -----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
4 L 4 7 5 kg pkg. 10.0000 4 20 5 0 4 G
W Tester 1 Hill Sydney NSW 2042 Australia
(02) 555-5914 (021) 555-2222 NULL
(1 rows affected)
1>
2> drop table Products;
3> drop table Suppliers;
4> GO
]]>