|
I have two tables, categories and items. A category can have zero to
many items, and each item can be active or inactive. I want to produce
a view that will show how many items, and how many active items are in
each category. So far the only way I have been able to do so is with
two intermediate/precursor views. I am looking to generate output like: Category Items ActiveItems 1 10 10 2 0 0 3 45 42 4 14 0 etc... I have my two precursor views count items and active items and then UNION with categories to catch any zero count categories. Then I build a new view from the first two. Is there a more efficient way to do this? My SQL is below. ItemsView SELECT category AS Category,(0) AS Items FROM categories WHERE category NOT IN (SELECT category FROM items) UNION SELECT category AS Category,COUNT item AS Items FROM items GROUP BY category ORDER BY category ActiveItemsView SELECT category AS Category,(0) AS ActiveItems FROM categories WHERE category NOT IN (SELECT category FROM items) UNION SELECT category AS Category,COUNT item AS ActiveItems FROM items WHERE status = 'ACTIVE' GROUP BY category ORDER BY category FinalView SELECT T1.Category,T2.Items,T3.ActiveItems FROM categories T1, ItemsView T2,ActiveItemsView T3 WHERE T1.Category = T2.Category AND T1.Category = T3.Category Thanks, Jason -- Jason Kramer University Archives and Records Management 002 Pearson Hall (302) 831 - 3127 (voice) (302) 831 - 6903 (fax) |
