Kc9400 at 13:07, 16 May 2013

2013-05-16T13:07:41Z

Connor at 09:32, 18 July 2012

2012-07-18T09:32:47Z

Connor at 09:32, 18 July 2012

2012-07-18T09:32:20Z

Connor: Created page with "

Edinburgh Buses: self join

A table may be joined with itself. When this happens we need some mechanism for distinguishing the instances of the table. ..."

2012-07-18T09:31:18Z

Created page with "<h3>Edinburgh Buses: self join</h3> <p>A table may be joined with itself. When this happens we need some mechanism for distinguishing the instances of the table. ..."

New page

<h3>Edinburgh Buses: self join</h3>

<p>A table may be joined with itself. When this happens we need
some mechanism for distinguishing the instances of the table.
Labels may be introduced in the FROM clause - a dot is used to
separate the label and attribute name when used elsewhere in
the statement.</p>

<h3>Examples of self joins on the route table</h3>

<h2>Self join of route on (num, company)</h2>

<p>We might join the route table on the (num, company) pair.
The result is a list of all pairs of stops which share a
service.</p>

<pre>
<b>SELECT * FROM route R1, route R2

WHERE R1.num=R2.num AND R1.company=R2.company</b>
</pre>

<p>This is a large table with over 11000 entries. An extract is
shown below. There are 121 entries the LRT 1 service alone.
There are 11 stops on the number 1. It is a circular route -
the first and last stops are identical.</p>

<table border="1" notranslate="1">
<tr>
<th>num</th>

<th>company</th>

<th>pos</th>

<th>stop</th>

<th>num_1</th>

<th>company_1</th>

<th>pos_1</th>

<th>stop_1</th>
</tr>

<tr>
<td>1</td>

<td>LRT</td>

<td align="right">1</td>

<td align="right">134</td>

<td>1</td>

<td>LRT</td>

<td align="right">7</td>

<td align="right">217</td>
</tr>

<tr>
<td>1</td>

<td>LRT</td>

<td align="right">1</td>

<td align="right">134</td>

<td>1</td>

<td>LRT</td>

<td align="right">11</td>

<td align="right">134</td>
</tr>

<tr>
<td>1</td>

<td>LRT</td>

<td align="right">1</td>

<td align="right">134</td>

<td>1</td>

<td>LRT</td>

<td align="right">10</td>

<td align="right">79</td>
</tr>

<tr>
<td colspan="8">...</td>
</tr>

<tr>
<td>1</td>

<td>LRT</td>

<td align="right">2</td>

<td align="right">97</td>

<td>1</td>

<td>LRT</td>

<td align="right">7</td>

<td align="right">217</td>
</tr>

<tr>
<td>1</td>

<td>LRT</td>

<td align="right">2</td>

<td align="right">97</td>

<td>1</td>

<td>LRT</td>

<td align="right">11</td>

<td align="right">134</td>
</tr>

<tr>
<td colspan="8">...</td>
</tr>

<tr>
<td>2</td>

<td>LRT</td>

<td align="right">9</td>

<td align="right">31</td>

<td>2</td>

<td>LRT</td>

<td align="right">11</td>

<td align="right">217</td>
</tr>

<tr>
<td>2</td>

<td>LRT</td>

<td align="right">9</td>

<td align="right">31</td>

<td>2</td>

<td>LRT</td>

<td align="right">1</td>

<td align="right">168</td>
</tr>

<tr>
<td colspan="8">...</td>
</tr>
</table>

<h3>Self join of route on stop</h3>

<p>We might join the route table with itself on the stop field.
The result is a list of all pairs of services which share a
stop.</p>

<pre>
<b>SELECT * FROM route R1, route R2

WHERE R1.stop=R2.stop;</b>
</pre>

<p>In the extract below some of the entries for stop 53,
'Craiglockhart' are shown.</p>

<table border="1" notranslate="1">
<tr>
<th>num</th>

<th>company</th>

<th>pos</th>

<th>stop</th>

<th>num_1</th>

<th>company_1</th>

<th>pos_1</th>

<th>stop_1</th>
</tr>

<tr>
<td>47</td>

<td>LRT</td>

<td align="right">4</td>

<td align="right">53</td>

<td>47</td>

<td>LRT</td>

<td align="right">4</td>

<td align="right">53</td>
</tr>

<tr>
<td>47</td>

<td>LRT</td>

<td align="right">4</td>

<td align="right">53</td>

<td>27</td>

<td>LRT</td>

<td align="right">6</td>

<td align="right">53</td>
</tr>

<tr>
<td>47</td>

<td>LRT</td>

<td align="right">4</td>

<td align="right">53</td>

<td>10</td>

<td>LRT</td>

<td align="right">8</td>

<td align="right">53</td>
</tr>

<tr>
<td>47</td>

<td>LRT</td>

<td align="right">4</td>

<td align="right">53</td>

<td>45</td>

<td>LRT</td>

<td align="right">7</td>

<td align="right">53</td>
</tr>

<tr>
<td>47</td>

<td>LRT</td>

<td align="right">4</td>

<td align="right">53</td>

<td>4</td>

<td>LRT</td>

<td align="right">6</td>

<td align="right">53</td>
</tr>

<tr>
<td>10</td>

<td>LRT</td>

<td align="right">8</td>

<td align="right">53</td>

<td>47</td>

<td>LRT</td>

<td align="right">4</td>

<td align="right">53</td>
</tr>

<tr>
<td>10</td>

<td>LRT</td>

<td align="right">8</td>

<td align="right">53</td>

<td>27</td>

<td>LRT</td>

<td align="right">6</td>

<td align="right">53</td>
</tr>

<tr>
<td>10</td>

<td>LRT</td>

<td align="right">8</td>

<td align="right">53</td>

<td>10</td>

<td>LRT</td>

<td align="right">8</td>

<td align="right">53</td>
</tr>

<tr>
<td colspan="8">...</td>
</tr>
</table>

@@ Line 16: / Line 16: @@
 <pre>
-'''SELECT * FROM route R1, route R2'''
+SELECT * FROM route R1, route R2
-   '''WHERE R1.num=R2.num AND R1.company=R2.company'''
+   WHERE R1.num=R2.num AND R1.company=R2.company
 </pre>
@@ Line 191: / Line 191: @@
 <pre>
-'''SELECT * FROM route R1, route R2'''
+SELECT * FROM route R1, route R2
-   '''WHERE R1.stop=R2.stop;'''
+   WHERE R1.stop=R2.stop;
 </pre>

@@ Line 16: / Line 16: @@
 <pre>
-<b>SELECT * FROM route R1, route R2
+'''SELECT * FROM route R1, route R2'''
-   WHERE R1.num=R2.num AND R1.company=R2.company</b>
+   '''WHERE R1.num=R2.num AND R1.company=R2.company'''
 </pre>
@@ Line 191: / Line 191: @@
 <pre>
-<b>SELECT * FROM route R1, route R2
+'''SELECT * FROM route R1, route R2'''
-   WHERE R1.stop=R2.stop;</b>
+   '''WHERE R1.stop=R2.stop;'''
 </pre>

@@ Line 22: / Line 22: @@
      <p>This is a large table with over 11000 entries. An extract is
-     shown below. There are 121 entries the LRT 1 service alone.
+     shown below. There are 121 entries for the LRT 1 service alone.
      There are 11 stops on the number 1. It is a circular route -
      the first and last stops are identical.</p>

Using a self join - Revision history

Kc9400 at 13:07, 16 May 2013

Connor at 09:32, 18 July 2012

Connor at 09:32, 18 July 2012

Connor: Created page with "

Edinburgh Buses: self join