Wednesday, 7 August 2013

getting the number of records having a specific condition and the the other that don't for the same card in the same query

getting the number of records having a specific condition and the the
other that don't for the same card in the same query

I have a table for which multiple child records can be linked in the
following way:
table object:
id description type
where id is a primary key.
I have another table, events that has as foreign key the object id
table event
id object_id status description
the status field can have a value 0 or 1 indicating if it's pending or not
I need to get in one query all objects and the number of events pending
and the number of events that are not
For example:
Object record:
id description type
---- ------------- ------
1 Printer hardware
Related event records
Event records
id object_id status description
---- --------- -------- --------------
1 1 0 Ordered
2 1 0 Shipped
3 1 1 Received by customer
The expected result is
object_id pending_events completed_events
----------- ----------------- -----------------
1 1 2
Thanks

No comments:

Post a Comment