A don't have a "definitive" answer, because in the SQL world there are many ways to do things (and I'm not a database expert),
but my first thought in reading what you are trying to do is:
have you researched using JOIN (inner or outer, I'm not sure,
depending on the details/specifics of how the data is organized internally to the tables).
You should also be able to do a SELECT WHERE on multiple tables, though,
either with, or without, using JOIN (but maybe including GROUP BY).
This "Using multiple tables in a query" article
my be helpful to glance through,
since it shows using SELECT with and without JOIN for multiple tables.
There is so much coding information of SQL JOINs out there, but its easy to get lost in all the specific examples for specific queries.
To understand them conceptually these pages (1
, 2 - scroll to page bottom
) have some nice diagrams.
As regards to CASE
, you haven't shown how you were thinking of using it,
but a lot of times people unfamiliar with its use in SQL try to use it as a "control of flow statement" when its actually an SQL expression.
Some example links (1
I've heard that CASE queries can be worrisome from a performance tuning standpoint (especially if they involve correlated subqueries
and it says as a caveat in this TechNet article
(near the end of the article):
Using simplified CASE expression statements results to have cleaner code and speed up development time,
but they show poor performance in some situations.
So if we are in performance tuning phase of software development,
it’s better to think about alternative solutions.