Forgotten rows - Revision history

Connor at 15:03, 2 August 2012

2012-08-02T15:03:41Z

2012-08-02T11:59:32Z

2012-08-02T11:59:16Z

2012-08-02T11:53:30Z

2012-08-02T11:49:43Z

2012-08-02T11:47:19Z

2012-08-02T11:46:31Z

2012-07-30T13:39:59Z

2012-07-30T13:07:58Z

2012-07-30T13:05:08Z

@@ Line 41: / Line 41: @@
 not include rows with a count of 0.</p>
   SELECT name, COUNT(custid)
- FROM customer JOIN invoice ON (id=custid)
+   FROM customer JOIN invoice ON (id=custid)
- GROUP BY name
+   GROUP BY name
 <p>In order to obtain the rows where the count from the query is 0 a
 <code>LEFT JOIN</code> or a <code>UNION</code> can be used.</p>
 </div>
-<source lang='sql' class='def'> SELECT name, COUNT(custid)
+<source lang='sql' class='def'>
- FROM customer LEFT JOIN invoice ON (id=custid)
+SELECT name, COUNT(custid)
-GROUP BY name</source>
+  FROM customer LEFT JOIN invoice ON (id=custid)
 <div class="ecomm e-mysql" style="display: none"></div>
 <div class="ecomm e-oracle" style="display: none"></div>
 </div>
 {{Hacks Ref}}

@@ Line 7: / Line 7: @@
 <tr><th align='center'>'''name'''</th><th>'''COUNT(custid)'''</th></tr>
 <tr><td align='left'>Betty</td><td align='left'>2</td></tr>
-<tr><td align='left'>Janette</td><td align='left'>1/td></tr>
+<tr><td align='left'>Janette</td><td align='left'>1</td></tr>
 </table>
 <table class="db_ref">

@@ Line 1: / Line 1: @@
 <p>In this example you are shown how to include the rows your <code>JOIN</code> function automatically leaves out as it does not perform the join when one of the values is 0.</p>
 <p>This is done by adding a either a <code>LEFT JOIN</code> or a <code>UNION</code> making the join also to reveal the rows with a count of 0.</p>
 <div class='ht'>
 <div class=params>schema:scott</div>

@@ Line 29: / Line 29: @@
 <code>LEFT JOIN</code> or a <code>UNION</code> can be used.</p>
 </div>
-<source lang='sql' class='def'> SELECT name, COUNT(*)
+<source lang='sql' class='def'> SELECT name, COUNT(custid)
   FROM customer LEFT JOIN invoice ON (id=custid)
 GROUP BY name</source>

@@ Line 23: / Line 23: @@
 <p>The following query will only give two rows as the JOIN function automatically does
 not include rows with a count of 0.</p>
-  SELECT name, COUNT(*)
+  SELECT name, COUNT(custid)
   FROM customer JOIN invoice ON (id=custid)
   GROUP BY name
@@ Line 30: / Line 30: @@
 </div>
 <source lang='sql' class='def'> SELECT name, COUNT(*)
-  FROM customer LEFT JOIN invoice ON (id=custid)</source>
+  FROM customer LEFT JOIN invoice ON (id=custid)
 <div class="ecomm e-mysql" style="display: none"></div>
 <div class="ecomm e-oracle" style="display: none"></div>
 </div>
 {{Hacks Ref}}

@@ Line 16: / Line 16: @@
    custid INTEGER,
    cost INTEGER );
-INSERT INTO invoice VALUES (1,2006-11-01,1,100);
+INSERT INTO invoice VALUES (1,'2006-11-01',1,100);
-INSERT INTO invoice VALUES (2,2006-11-05,1,500);
+INSERT INTO invoice VALUES (2,'2006-11-05',1,500);
-INSERT INTO invoice VALUES (3,2006-11-11,3,200);
+INSERT INTO invoice VALUES (3,'2006-11-11',3,200);
 </source>
 <div>

@@ Line 10: / Line 10: @@
 INSERT INTO customer VALUES (2,'Robert');
 INSERT INTO customer VALUES (3,'Janette');
-CREATE TABLE invoice (
+CREATE TABLE invoice(
    invoiceno INTEGER,
    whn DATE,