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

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

background:

i'm running query gets total widgets, sprockets, , gizmos ordered client during lastly month i have total orders column runs subquery counting every order in month the reason total orders in subquery, instead of adding 3 columns, because have farther 2 total orders columns 2 previous months i have orders table stores financial records, , separate tables actual product details widgets, sprockets, , gizmos

the problem:

occasionally, widget might 'half-deleted' (don't ask!) - has orders record, not corresponding widgets record i not want count in total widgets column - easy enough, join however, not want count in total orders column...

my current query looks total widgets:

select count(orders.id) orders bring together widgets on widgets.id = orders.item_id orders.product_id = 1 -- product id 1 widget , orders.date between "2014-09-01 00:00:00" , "2014-09-30 23:59:59"

so 'accurate' widgets, intact widget table record.

here current query total orders:

select count(orders.id) count orders bring together widgets on widgets.id = orders.item_id , orders.product_id = 1 orders.date between "2014-09-01 00:00:00" , "2014-09-30 23:59:59"

so thinking above query should join when order has product_id of 1. however, joins in every case. means if we've ordered 10 widgets (2 of have been half-deleted), 5 sprockets, , 5 gizmos, rather showing 18 orders, shows 8. changing left join shows 20, still wrong, should 18.

hopefully above makes sense - in advance.

okay so... straight bring together doesn't work because filter orders.product_id = 1 , won't count sprockets or gizmos. widgets. , if have 2 half deleted, 10 on record... 8 right answer. left outer join lets bring together on rows product_id <> 1, too, rows orders (which contains order records on widgets, sprockets, gizmos - half deleted or not). allows 2 half deleted rows.

why don't straight join? if want reply 18, bring together orders , products , count it. not filter widgets only. not left outer if don't want half deleted.

create table product ( product_id int, product_name varchar(20) ); insert product values (1,'widget'); insert product values (2,'sprocket'); insert product values (3,'gizmo'); create table `order` ( order_id int, widget_id int ); insert `order` values (1,1); insert `order` values (1,2); insert `order` values (1,3); insert `order` values (1,4); -- half deleted insert `order` values (2,1); insert `order` values (2,2); insert `order` values (3,3); insert `order` values (3,4); -- half deleted insert `order` values (4,4); -- half deleted select o.order_id, count(*) `order` o bring together product p on o.widget_id=p.product_id grouping 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 null -> 'half_deleted' else '' end) half_deleted -> `order` o -> left outer bring together product p -> on o.widget_id=p.product_id -> order 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 sql join