SQL query with CASE or subquery?

[bwwhite]

My database stores bowling scores from my bowling league. I have a table of bowlers' names and their previous year's averages and handicaps. I have another table to record current weekly league results.

My 2 Access data tables look like this

BowlerTable:
year
bowler_id
team_id
lyr_ave
lyr_hcp

ResultsTable:
year
week
bowler_id
team_id
game_num
score
average
handicap

The two tables are related by year, bowler_id, and team_id.

I wonder if it is possible, in a single SQL query using UNION or CASE or subqueries, to do the following:

SELECT year, bowler_id, team_id, lyr_ave, lyr_hcp
FROM BowlerTable

I know the above is possible, but then I want to:

Find each bowler's name in ResultsTable, count and display how many games they have bowled this year prior to a given week, and sum their total scores. In doing this, I also need to test to see if their current year's game count is < 15. If so, I need to use the value of lyr_ave from BowlerTable to help determine their current average.

So--can I do all this in a single SQL query to my database?

Bruce

There are only 10 kinds of people in the world--those who understand binary and those who don't.

 

[lespaul]

Here's my best guess. Do you really calculate the average if it's LESS than 15 weeks? Any way, this is typed, not tested, and I don't know what formula you would need to put to calculate the average, but the formula would go in the "Less than 15" spot.

SELECT B.year, B.bowler_id, B.team_id, B.lyr_ave, B.lyr_hcp, count(R.[week]), SUM(R.Score), iif(count(r.[week] < 15, "Less Than 15", "More Than 15")
FROM BowlerTable B
INNER JOIN ResultsTable R on B.Bowler_ID = R.Bowler_ID and B.Team_ID = R.teamID and B.[Year] = R.[Year]
GROUP BY B.year, B.bowler_id, B.team_id, B.lyr_ave, B.lyr_hcp, iif(count(r.[week] < 15, "Less Than 15", "More Than 15")

Leslie

Have you met Hardy Heron?

 

[bwwhite]

Thanks, lespaul! I'm going to try what you suggest, but I'm also wondering if there is a way to do it using one or more subqueries. My application is using ASP.NET and my data access layer prefers subqueries to joins (at least that's how I've interpreted the warnings I've seen.

I will post back here on the outcome of your code.

Thanks again!

Bruce

There are only 10 kinds of people in the world--those who understand binary and those who don't.

 

[lespaul]

uummmm...subqueries and joins are really two different things.....check out Understanding SQL Joins

HTH

Leslie