Difference between revisions of "Subquery and JOIN2"

From SQLZOO
Jump to: navigation, search
Line 14: Line 14:
 
<div>
 
<div>
 
  SELECT customer, whn, totalitems
 
  SELECT customer, whn, totalitems
  FROM orders 01
+
  FROM orders a
  WHERE 01.whn = (
+
  WHERE a.whn = (
 
   SELECT MAX(whn)
 
   SELECT MAX(whn)
   FROM orders 02
+
   FROM orders b
   WHERE 01.customer = 02.customer)
+
   WHERE a.customer = b.customer)
 
To make this more efficient a HAVING clause can be used with
 
To make this more efficient a HAVING clause can be used with
 
a self join.
 
a self join.
 
</div>
 
</div>
<source lang='sql' class='def'>SELECT 01.customer, 01.whn, 01.totalitems
+
<source lang='sql' class='def'>SELECT a.customer, a.whn, a.totalitems
FROM orders 01 JOIN orders 02 ON (01.customer = 02.customer)
+
FROM orders a JOIN orders b ON (a.customer = b.customer)
GROUP BY 01.customer, 01.whn, 01.totalitems
+
GROUP BY a.customer, a.whn, a.totalitems
HAVING 01.whn = MAX(02.whn)</source>
+
HAVING a.whn = MAX(b.whn)</source>
 
<div class="ecomm e-mysql" style="display: none"></div>
 
<div class="ecomm e-mysql" style="display: none"></div>
 
</div>
 
</div>
 
{{Hacks Ref}}
 
{{Hacks Ref}}

Revision as of 10:44, 30 July 2012

Converting aggregate subqueries into Join functions.

schema:scott
DROP TABLE orders
 CREATE TABLE orders(
  employee VARCHAR(40)),
  whn DATE(YYYY,MM,DD),
  totalitems INTEGER));
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,12), 1);
INSERT INTO orders VALUES ('Brian',(2006,10,10), 7);
SELECT customer, whn, totalitems
FROM orders a
WHERE a.whn = (
 SELECT MAX(whn)
 FROM orders b
 WHERE a.customer = b.customer)

To make this more efficient a HAVING clause can be used with a self join.

SELECT a.customer, a.whn, a.totalitems
FROM orders a JOIN orders b ON (a.customer = b.customer)
GROUP BY a.customer, a.whn, a.totalitems
HAVING a.whn = MAX(b.whn)
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense