Need Query Help

has to be a better way...

[txbakers]

What I have works, but I think there could be a better/faster way of retreiving the data.

mySql 3.23 (no unions at this point).

Here is the data:
Three tables, each with separate data (no joins)
T1 has 5 categories
T2 has 2 cats
T3 has 1 category

Each table also has a user code.

The page is a report of a contest entry count, sorted by Entrant, with 8 columns of data, the sum of each category, plus a grand total by entrant and category counts on the bottom.

Right now I have 1 query to pull a list of entrants. The page begins and lists the first entrant. Then I run 8 queries, using that entrant as the key to the three tables, and add the sums for each category. I keep a set of globals for the totals.

As I said, it works, but I think there could be a better way.

I thought about a "group by" at the top (by entrant) but some entrants aren't in all the categories, and I need to show a zero.

Any ideas I could be missing?

[txbakers]

Never mind, found a better way.

If I reduce it to three queries, one for each table, I can let the ASP engine calculate the totals with case/selects and if/thens. That's faster than multiple queries with aggregates.

Much faster now.

[IanTurner]

Not sure whether MySQL supports the following but you could look at

ISNULL(count(group), 0)

or the CASE statement

Simple CASE function:

CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Searched CASE function:

CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END