How can I join a table in MySQL, deriving one boolean result based on some criteria? -

How can I join a table in MySQL, deriving one boolean result based on some criteria? -

i have 3 tables joining in mysql. simplicity's sake, let's phone call them customers, orders , notes. goal create 1 query used in windows application generate list of customers, number of orders have, , flag indicate if human has written notes.

before added requirement of notes, query was:

select c.customer `customer`, count(o.id) `order qty` customers c left bring together orders o on o.customer_id = c.id grouping c.customer asc;

which produces:

customer order qty -------- --------- acme corp. 2 bee inc. 3

i'd add together boolean column indicate if human entered notes exist. (there automatically generated notes, ignored query.)

the notes table basically:

id int(3) customer_id int(3) note_datetime datetime is_auto tinyint(1) note text

any client may have number of notes (one many relationship), , notes may automatically generated (is_auto true) or not.

i'd add together column returns true if any of notes client not automatic, letting users know there human-entered notes read.

for example, acme (1) has 3 notes, 1 of not automatic. bee (2) has 2 automatic notes:

id customer_id is_auto -- ----------- ------- 1 1 1 2 1 1 3 1 0 4 2 1 5 2 1

i've come next query:

select c.customer `customer`, count(o.id) `order qty`, if(sum(if(n.is_auto, 0, 1) > 0), true, false) `human notes` customers c left bring together orders o on o.customer_id = c.id left bring together notes n on n.customer_id = c.id grouping c.customer asc;

this produces:

customer order qty human notes -------- --------- ----------- acme corp. 6 true # (6 orders, should 2) bee inc. 6 false # (6 orders, should 1)

the problem rows notes table inflates number of orders artificially (2 orders multiplied 3 notes; , 3 orders multiplied 2 notes). understand because left bring together creates duplicate rows of orders match multiple rows notes table.

i have not been able determine proper way rewrite query. improve way bring together notes table desired boolean, avoid rows alter order count?

try using exists instead:

select c.customer, count(o.id) `order qty`, (case when exists (select 1 notes n n.customer_id = c.id , not n.is_auto) true else false end) `human notes` customers c left bring together orders o on o.customer_id = c.id grouping c.customer asc;

otherwise, number of notes messes count orders -- discovered.

mysql left-join