Getting counts during a 'drill-down' query

Databases
1

I have a hierarchical structure as follows:
lev1tbl (lev1id, lev1name) — 11 records
lev2tbl(lev2id, lev1id, lev2name) — 66 records
lev3tbl(lev3id, lev2id, lev3name) — 236 records, may increase to 2000
lev4tbl(lev4id, lev3id, lev4name, tbl5id) — potentially very large 1 million +
tbl5(tbl5id, tbl5location) about 400000recs, indexed on location

It is a drill down situation, so first I present the values in tbl1, they click on one of those and it presents the related values in tbl2, etc.

What I would like to do is at each level present them a count of the records in tbl4 that match the where criteria for the tbl5 location. So obviously that count would decrease as I go down the categories.

So I would need to do the count when I present lev1tbl, lev2tbl and lev3tbl.

I am not against restructuring the tables to make some of the criteria redundant. Given the number of rows in each table and the fact that at least at level 1 and level 2, the data is very static. If a change were required, I could handle it programatically.

2

Okay, case closed. I finally got it.

3

Okay, I figured out how to do it as

SELECT tbl1name, count(*)
FROM tbl2
INNER JOIN tbl1 ON tbl1.tbl1id = tbl2.tbl1id
inner join tbl3 on tbl3.tbl2id = tbl2.tbl2id
inner join tbl4 on tbl4.tbl3id = tbl3.tbl3id
group by tbl1name

So now a best practices question, is this the best way to do this?

4

Update *** that doesn’t work if there are no records in tbl4 that relate to tbl1id. What I want to happen on the first query is to show all records in tbl1 with a count for the number of related records in tbl4 and if there are no records in tbl4 I want it to show 0 or null. But it has to list all the records in tbl1 regardless if there are related records in tbl4.

Thanks

5

Closer. I am able to get the counts that I need but when I add the where clause it goes back to an inner join again and I only get results from tbl1 when there are records in tbl4.

So this works, but without a where clause:

SELECT tbl1name, tbl2.tbl2id, tbl3.tbl3id, tbl4.tbl4id, count(tbl4.tbl4id)
FROM tbl2
LEFT JOIN tbl1 ON tbl1.tbl1id = tbl2.tbl1id
LEFT JOIN tbl3 on tbl3.tbl2id = tbl2.tbl2id
LEFT JOIN tbl4 on tbl4.tbl3id = tbl3.tbl3id
GROUP BY tbl1name
ORDER BY tbl1.sort

But if I add a where clause, it goes back to only giving me rows from tbl1 where there are results in tbl4

SELECT tbl1name, tbl2.tbl2id, tbl3.tbl3id, tbl4.tbl4id, count(tbl4.tbl4id)
FROM tbl2
LEFT JOIN tbl1 ON tbl1.tbl1id = tbl2.tbl1id
LEFT JOIN tbl3 on tbl3.tbl2id = tbl2.tbl2id
LEFT JOIN tbl4 on tbl4.tbl3id = tbl3.tbl3id
LEFT JOIN tbl5 on tbl4.tbl5id = tbl5.tbl5id
WHERE tbl5.location = ‘Anywhere’

Note: There will always be a record in tbl5 for the location id in tbl4.

Thanks
GROUP BY tbl1name
ORDER BY tbl1.sort