SQL Query

[DKL01]

MS SQLServer 7.0

TABLE1:

document_id, confidential_cd
1, "show"
2, "hide"
7, "show"
9, "show"
10, "hide"
12, "show"
18, "show"
20, "show"

***************************

TABLE2:

document_id, category_id
1, A
2, A
7, A
9, B
10, B
12, C
18, D
20, E

*********************

TABLE3:

category_id, Department
A, 1
B, 1
C, 1
D, 1
E, 7
F, 7

***************************

TABLE4:

document_id
2


I need to write a query to get number of documents in each Department

Department, document_count
1, 6
7 1

The only catch in the logic is we should not consider documents with confidential_cd of "hide" unless it appears in TABLE4.
I tried with using temp tables. But it is very slow as we have thousands of records.
I really appreciate any good logic.

Thanks

 

[hirick]

You need something like this:

Select department, count(table2.document_id)
From table3 Inner Join
(table2 Inner Join
(Select table1.document_id From table1
Where conf_code='show' Or
table1.document_id In
(Select document_id From table4))
On table1.document_id=table2.document_id)
On table3.category_id=table2.category_id
Group By department