Difference between revisions of "Forgotten rows"
| Line 26: | Line 26: | ||
GROUP BY name | GROUP BY name | ||
<p>In order to obtain the rows where the count from the query is 0 a | <p>In order to obtain the rows where the count from the query is 0 a | ||
| − | LEFT JOIN or a UNION can be used.</p> | + | <code>LEFT JOIN</code> or a <code>UNION</code> can be used.</p> |
</div> | </div> | ||
<source lang='sql' class='def'> SELECT name, COUNT(*) | <source lang='sql' class='def'> SELECT name, COUNT(*) | ||
Revision as of 13:39, 30 July 2012
Include the rows your JOIN forgot.
DROP TABLE customer;
DROP TABLE invoice;
CREATE TABLE customer(
id INTEGER,
name VARCHAR(20));
INSERT INTO customer VALUES (1,'Betty');
INSERT INTO customer VALUES (2,'Robert');
INSERT INTO customer VALUES (3,'Janette');
CREATE TABLE invoice(
invoiceno INTEGER,
whn DATE,
custid INTEGER,
cost INTEGER );
INSERT INTO invoice VALUES (1,(2006,11,01),1,100);
INSERT INTO invoice VALUES (2,(2006,11,05),1,500);
INSERT INTO invoice VALUES (3,(2006,11,11),3,200);
The following query will only give two rows as the JOIN function automatically does not include rows with a count of 0.
SELECT name, COUNT(*) FROM customer JOIN invoice ON (id=custid) GROUP BY name
In order to obtain the rows where the count from the query is 0 a
LEFT JOIN or a UNION can be used.
SELECT name, COUNT(*)
FROM customer LEFT JOIN invoice ON (id=custid)
Hack 10 Converting subqueries into joins
Hack 11 Converting aggregate subqueries into joins
Hack 16 Search for a String across columns
Hack 24 Multiply Across a Result Set
Hack 25.5 Splitting and combining columns
Hack 26 Include the rows your JOIN forgot
Hack 30 Calculate the maximum/minimum of two fields
Hack 33 Get values and subtotals in one shot
Hack 50 Combine tables containing different data
Hack 51/52 Display rows as columns
Hack 55 Import Someone Else's Data
Hack 62 Issue Queries Without Using a Table
Hack 63 Generate rows without tables
Hack 72 Extract a subset of the results
Hack 78 Break it down by Range
Hack 88 Test two values from a subquery
