Difference between revisions of "Forgotten rows"

From SQLZOO
Jump to: navigation, search
(Created page with "Include the rows your JOIN forgot. <div class='ht'> <div class=params>schema:scott</div> <source lang=sql class='tidy'>DROP TABLE customer; DROP TABLE invoice;</source> <sourc...")
 
Line 10: Line 10:
 
INSERT INTO customer VALUES (2,'Robert');
 
INSERT INTO customer VALUES (2,'Robert');
 
INSERT INTO customer VALUES (3,'Janette');
 
INSERT INTO customer VALUES (3,'Janette');
CREATE TABLE invoice
+
CREATE TABLE invoice (
 
   invoiceno INTEGER,
 
   invoiceno INTEGER,
 
   whn DATE,
 
   whn DATE,
 
   custid INTEGER,
 
   custid INTEGER,
   cost 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);

Revision as of 14:03, 30 July 2012

Include the rows your JOIN forgot.

schema:scott
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)
SELECT name, COUNT(*)
FROM customer JOIN invoice ON (id=custid)
UNION
SELECT name, 0
FROM customer
WHERE id NOT IN (SELECT custid FROM invoice)
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense