Mega Code Archive
Joining a Table with Itself
<![CDATA[
14>
15> CREATE TABLE department(dept_no CHAR(4) NOT NULL,
16> dept_name CHAR(25) NOT NULL,
17> location CHAR(30) NULL)
18>
19> insert into department values ('d1', 'developer', 'Dallas')
20> insert into department values ('d2', 'tester', 'Seattle')
21> insert into department values ('d3', 'marketing', 'Dallas')
22>
23> select * from department
24> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
dept_no dept_name location
------- ------------------------- ------------------------------
d1 developer Dallas
d2 tester Seattle
d3 marketing Dallas
(3 rows affected)
1> -- Joining a Table with Itself
2>
3> -- ANSI join syntax:
4>
5> SELECT t1.dept_no, t1.dept_name, t1.location
6> FROM department t1 JOIN department t2
7> ON t1.location=t2.location
8> WHERE t1.dept_no <> t2.dept_no
9> GO
dept_no dept_name location
------- ------------------------- ------------------------------
d3 marketing Dallas
d1 developer Dallas
(2 rows affected)
1> -- SQL Server join syntax:
2>
3> SELECT DISTINCT t1.dept_no, t1.dept_name, t1.location
4> FROM department t1, department t2
5> WHERE t1. location = t2.location
6> AND t1.dept_no <> t2.dept_no
7> GO
dept_no dept_name location
------- ------------------------- ------------------------------
d1 developer Dallas
d3 marketing Dallas
(2 rows affected)
1>
2> drop table department
3> GO
1>
2>
]]>