Multiple table query

[Mchl]

I have three tables

CREATE TABLE  `products` (
  `productID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`productID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE  `invoices` (
  `invoiceID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('PENDING','ACCEPTED') NOT NULL,
  PRIMARY KEY (`invoiceID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE  `invoicerows` (
  `rowID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoiceID` int(10) unsigned NOT NULL,
  `productID` int(10) unsigned NOT NULL,
  `price` decimal(8,2) NOT NULL,
  `amount` int(10) unsigned NOT NULL,
  PRIMARY KEY (`rowID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO products (name) VALUES ('a'),('b'),('c'),('d'),('e'),('f');

INSERT INTO invoices (status) VALUES ('PENDING'),('PENDING'),('ACCEPTED'),('ACCEPTED');

INSERT INTO invoicerows (invoiceID,productID,price,amount) VALUES (1,1,1,1),(1,2,1,1),(2,3,1,1),(2,4,1,1),(3,1,1,1),(3,3,1,1),(4,2,1,1),(4,4,1,1);

 

Now when I want to have list of ALL products and how many of them are on invoices, I can use query like this

SELECT name, SUM(amount) FROM products AS p LEFT JOIN invoicerows AS ir USING (productID) LEFT JOIN invoices AS i USING (invoiceID) GROUP BY name

 

This returns 6 rows, even if only 4 of the 6 products are on invoices. This is how I want it.

 

Now I'd like to get list of all products, and their amounts on ACCEPTED invoices

 

SELECT name, SUM(amount) FROM products AS p LEFT JOIN invoicerows AS ir USING (productID) LEFT JOIN invoices AS i USING (invoiceID) WHERE status = 'ACCEPTED' GROUP BY name

 

This query will give me proper sums, but only 4 products listed (and I want all, even if some of them aren't on invoices)

 

Can this query be modified to give this resultset?

name,SUM(amount)
a , 1
b , 1
c , 1
d , 1
e , NULL
f , NULL

 

[Mchl]

OK. So I've come up with this

 

SELECT name, SUM(amount) FROM products AS p LEFT JOIN (SELECT * FROM invoicerows AS ir LEFT JOIN invoices AS i USING (invoiceID) WHERE status='ACCEPTED') AS sq USING (productID) GROUP BY name

 

So basically filter invoices first, select products later...

I just hope it will outperform my multi-subquery query I've been using so for to get these results.

 

 

Any other thoughts on that?