Re: Getting full-like join with GROUP BY

[Peter Brawley]

> have all intervals in the resultset for each category even if there is no sale for a category.

MySQL doesn't implement Full Outer Join, of course you can write one, but in any RDBMS it would be an expensive solution.

Simpler:

create table categories(category char(10) primary key) ;
insert into categories values("foreign"),("domestic");

Then ...

select c.category, pi.lower, pi.higher, coalesce(sum(price),0) as Total
from price_intervals pi
cross join categories c  -- ALL INTERVALS AND ALL CATS
left join sales s on pi.id = s.interval_id and s.category=c.category
group by pi.id, s.category 
order by pi.lower, s.category; 
+----------+-------+--------+-------+
| category | lower | higher | Total |
+----------+-------+--------+-------+
| domestic |     0 |    100 |   160 |
| foreign  |     0 |    100 |   130 |
| domestic |   100 |    200 |     0 |
| domestic |   200 |    300 |   210 |
| foreign  |   200 |    300 |   250 |
| domestic |   300 |    400 |     0 |
| domestic |   400 |    500 |     0 |
+----------+-------+--------+-------+

(To enforce the category sales rule, add a categories FK to sales.)