MySQL JOIN "WHEN"- is such a thing possible?

Question

Background:

I'm running a query which gets the total widgets, sprockets, and gizmos ordered by the customer during the last month
I also have a Total Orders column which runs a subquery counting every order in that month
The reason I do Total Orders in a subquery, instead of just adding the three columns, is because I also have a further two Total Orders columns for the two previous months
I have an orders table that stores financial records, and separate tables for actual product details for widgets, sprockets, and gizmos

The problem:

Occasionally, a widget might be 'half-deleted' (don't ask!) - where it has an orders record, but not a corresponding widgets record
I do not want to count this in the Total Widgets column - which is easy enough, as I just do a JOIN
However, I also do not want to count this in the Total Orders column...

My current query looks like this for Total Widgets:

SELECT 
    COUNT(orders.id)
FROM orders
JOIN widgets ON widgets.id = orders.item_id
WHERE orders.product_id = 1 -- Product ID 1 is a Widget
AND orders.date BETWEEN "2014-09-01 00:00:00" AND "2014-09-30 23:59:59"

So this will get all 'accurate' widgets, with an intact widget table record.

Here is my current query for Total Orders:

SELECT 
    COUNT(orders.id) AS count
FROM orders
JOIN widgets ON widgets.id = orders.item_id AND orders.product_id = 1
WHERE orders.date BETWEEN "2014-09-01 00:00:00" AND "2014-09-30 23:59:59"

So my thinking is that the above query should only JOIN when the order has a product_id of 1. However, it JOINs in every case. Which means if we've ordered 10 widgets (2 of which have been half-deleted), 5 sprockets, and 5 gizmos, rather than showing 18 orders, it only shows 8. Changing to a LEFT JOIN shows 20, which is still wrong, it should be 18.

Hopefully the above makes sense - thanks in advance.

so... it's really early for me. can you post some sample data from each table (with some data that will be returned and some that won't) and an example result set..? — gloomy.penguin, Oct 13 '14 at 12:16
Are you new, semi-new to SQL. I only skimmed your question, but it sounds to me like you want to use an `OUTER JOIN`. The following link is an answer for SQL Server, but basic SQL is mostly the same across architectures. This should help you. (http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server) — RLH, Oct 13 '14 at 12:17
i am so confused. sorry, it's early. you don't need a `sum(case)`... okay so... the straight join doesn't work because you filter for orders.product_id = 1 and so it won't count sprockets or gizmos. just widgets. and if you have 2 half deleted, but 10 on record... 8 is the correct answer. the left outer join lets it join on rows where product_id <> 1, too, so all rows from orders (which contains order records on widgets, sprockets, gizmos - half deleted or not). but this also allows those 2 half deleted rows — gloomy.penguin, Oct 13 '14 at 12:42
so... why don't you just do a straight join? if you want the answer 18, just join orders and products and count it. do not filter for widgets only. do not do a left outer if you don't want half deleted. — gloomy.penguin, Oct 13 '14 at 12:43

gloomy.penguin · Answer 1 · 2014-10-13T12:50:46.327

okay so... the straight join doesn't work because you filter for orders.product_id = 1 and so it won't count sprockets or gizmos. just widgets. and if you have 2 half deleted, but 10 on record... 8 is the correct answer. the left outer join lets it join on rows where product_id <> 1, too, so all rows from orders (which contains order records on widgets, sprockets, gizmos - half deleted or not). but this also allows those 2 half deleted rows.

why don't you just do a straight join? if you want the answer 18, just join orders and products and count it. do not filter for widgets only. do not do a left outer if you don't want half deleted.

create table product (
   product_id int,
   product_name varchar(20)
);

insert into product values (1,'widget'); 
insert into product values (2,'sprocket'); 
insert into product values (3,'gizmo'); 

create table `order` (
   order_id  int,
   widget_id int  
); 

insert into `order` values (1,1); 
insert into `order` values (1,2); 
insert into `order` values (1,3); 
insert into `order` values (1,4);  -- half deleted
insert into `order` values (2,1); 
insert into `order` values (2,2);  
insert into `order` values (3,3); 
insert into `order` values (3,4);  -- half deleted  
insert into `order` values (4,4);  -- half deleted 



select   o.order_id, count(*)
from     `order` as o 
         join product as p  
            on o.widget_id=p.product_id   
group    by o.order_id     


+----------+----------+
| order_id | count(*) |
+----------+----------+
|        1 |        3 |
|        2 |        2 |
|        3 |        1 |
+----------+----------+
3 rows in set (0.00 sec)

mysql> select   o.order_id,
    ->          p.product_name,
    ->          (case when p.product_id is null
    ->                then 'half_deleted' else '' end) as half_deleted
    -> from     `order` as o
    ->          left outer join product as p
    ->             on o.widget_id=p.product_id
    -> order    by order_id   ;
+----------+--------------+--------------+
| order_id | product_name | half_deleted |
+----------+--------------+--------------+
|        1 | gizmo        |              |
|        1 | NULL         | half_deleted |
|        1 | widget       |              |
|        1 | sprocket     |              |
|        2 | widget       |              |
|        2 | sprocket     |              |
|        3 | gizmo        |              |
|        3 | NULL         | half_deleted |
|        4 | NULL         | half_deleted |
+----------+--------------+--------------+

MySQL JOIN "WHEN"- is such a thing possible?

1 Answers1