Subquery and JOIN2 - Revision history

Connor at 14:59, 2 August 2012

2012-08-02T14:59:23Z

2012-08-02T10:06:17Z

2012-08-02T09:55:35Z

2012-07-30T13:35:18Z

2012-07-30T10:25:39Z

2012-07-30T10:24:44Z

2012-07-30T10:23:42Z

2012-07-30T10:21:42Z

2012-07-30T09:44:13Z

2012-07-30T09:43:08Z

@@ Line 23: / Line 23: @@
 a self join to replace the subquery.
 </div>
-<source lang='sql' class='def'>SELECT a.customer, a.whn, a.totalitems
+<source lang='sql' class='def'>
-FROM orders a JOIN orders b ON (a.customer = b.customer)
+SELECT a.customer, a.whn, a.totalitems
-GROUP BY a.customer, a.whn, a.totalitems
+  FROM orders a JOIN orders b ON (a.customer = b.customer)
-HAVING a.whn = MAX(b.whn)</source>
+  GROUP BY a.customer, a.whn, a.totalitems
 <div class="ecomm e-mysql" style="display: none"></div>
 </div>
 {{Hacks Ref}}

@@ Line 17: / Line 17: @@
   FROM orders a
   WHERE a.whn = (
-   SELECT MAX(whn)
+   SELECT MAX(totalitems)
    FROM orders b
    WHERE a.customer = b.customer)

@@ Line 1: / Line 1: @@
-Converting aggregate subqueries into Join functions.
+In this example you are shown how to convert subqueries containing aggregate functions into
 <div class='ht'>
 <div class=params>schema:scott</div>
@@ Line 19: / Line 20: @@
    FROM orders b
    WHERE a.customer = b.customer)
-To make this more efficient a <code>HAVING</code> clause can be used with
+To make this query more efficient a <code>HAVING</code> clause can be used with
-a self join.
+a self join to replace the subquery.
 </div>
 <source lang='sql' class='def'>SELECT a.customer, a.whn, a.totalitems

@@ Line 19: / Line 19: @@
    FROM orders b
    WHERE a.customer = b.customer)
-To make this more efficient a HAVING clause can be used with
+To make this more efficient a <code>HAVING</code> clause can be used with
 a self join.
 </div>

@@ Line 4: / Line 4: @@
 <source lang=sql class='tidy'>DROP TABLE orders</source>
 <source lang=sql class='setup'> CREATE TABLE orders(
-   employee VARCHAR(40),
+   customer VARCHAR(40),
    whn VARCHAR(40),
    totalitems INTEGER );

@@ Line 5: / Line 5: @@
 <source lang=sql class='setup'> CREATE TABLE orders(
    employee VARCHAR(40),
-   whn DATE(YYYY,MM,DD),
+   whn VARCHAR(40),
    totalitems INTEGER );
-INSERT INTO orders VALUES ('Jim',(2006,10,10), 5);
+INSERT INTO orders VALUES ('Jim','2006-10-10', 5);
-INSERT INTO orders VALUES ('Jim',(2006,10,11), 3);
+INSERT INTO orders VALUES ('Jim','2006-10-11', 3);
-INSERT INTO orders VALUES ('Jim',(2006,10,12), 1);
+INSERT INTO orders VALUES ('Jim','2006-10-12', 1);
-INSERT INTO orders VALUES ('Brian',(2006,10,10), 7);
+INSERT INTO orders VALUES ('Brian','2006-10-10', 7);
 </source>
 <div>