MySQL: Select orders that have completed lines -

this has got simple.

i have 2 tables: orders , order_lines. pretty simple , self-explanatory; one-to-many relationship. order_lines has fulfilled column containing date when particular line completed.

here's need do: select orders.id of orders complete; i.e. none of fulfilled columns in order_lines table null particular order.

example:

order #1 has 2 lines, both of not null

order #2 has 1 line null

the query should return #1 because complete.

select * orders o not exists    (                     select 1                     order_lines ol1                     ol1.fullfilled null                          , ol1.orderid = o.orderid                     ) 

sql fiddle version

an alternate version uses in function:

select * orders o o.orderid not in    (                           select ol1.orderid                           order_lines ol1                           ol1.fullfilled null                           ) 

sql fiddle version

addition

there desire accomplish using aggregate function. while below solution, forms use exists , in function express intent better following query , i'd recommend 1 of 2 forms over:

select o.orderid orders o     left join order_lines ol         on ol.orderid = o.orderid             , ol.fullfilled null group o.orderid having count(ol.id) = 0 

sql fiddle version

yet form perform best:

select o.orderid orders o     left join order_lines ol         on ol.orderid = o.orderid             , ol.fullfilled null ol.id null 

sql fiddle version