Select elements of child tables with a given property
I am really not good at sql, The problem is the following: I have a metadata table like this
META:
id(pk) prop1 prop2
1 'pr1' 'pr2'
2 'pr1' 'pr2'
At the same time, I have many child tables which look something like this:
TABLE1:
metaID(fk-pk) indx(pk) active ...
1 1 1 ...
1 2 0 ...
1 3 1 ...
2 1 1 ...
and a second table similar to the first
TABLE2:
metaID(fk-pk) indx(pk) active ...
1 1 1 ...
1 2 1 ...
2 1 0 ...
2 2 0 ...
I need a qu开发者_JAVA百科ery which retrieves the the metadata table properties and the number of records in each child table which are active.
For example if I want the elements with prop1 = 'pr1
the query should answer:
meta-id prop1 prop2 TAB1CNT TAB2CNT
1 'pr1' 'pr2' 2 2
2 'pr1' 'pr2' 1 0
where TAB1CNT
is the count of the active elements of TABLE1
and the same goes for TAB2CNT
Something like this should do the trick. This would return all meta records, regardless of if there were any active records in table1 or table2.
SELECT ID, prop1, prop2, ISNULL(t1.ActiveCount, 0) AS TAB1CNT, ISNULL(t2.ActiveCount, 0) AS TAB2CNT
FROM META m
LEFT JOIN
(
SELECT metaID, COUNT(*) AS ActiveCount
FROM TABLE1
WHERE active = 1
GROUP BY metaID
) t1 ON m.ID = t1.metaID
LEFT JOIN
(
SELECT metaID, COUNT(*) AS ActiveCount
FROM TABLE2
WHERE active = 1
GROUP BY metaID
) t2 ON m.ID = t2.metaID
Hopefully from this, you can then add whatever clauses you want to return only the META records you are interested in.
精彩评论