Programming Question - SQL Help Needed

[21C]

I've been playing around with the Lahman baseball database ( Access 2000 ) in Visual Basic 6 and successfully queried it to get team records for home runs or hits but I'm just trying to improve it a little.

So far, I've found a team record by grouping everything by the Batting.teamID. The only problem is that this creates separate records for Anaheim, California and LA Angels, for example, while it should be one that encompasses all of these. There is a Teams.franchID that links these but this is where I get stuck.

This is the query I used which gave separate results:

"SELECT TOP 20 Master.nameLast, Master.nameFirst, Batting.teamID, " & _
"SUM(Batting.G) AS G, MAX(Batting.yearID) AS LastYear " & _
"FROM Batting INNER JOIN Master ON Batting.playerID = Master.playerID " & _
"GROUP BY Master.nameLast, Master.nameFirst, Batting.teamID, " & _
"Batting.teamID, Batting.playerID " & _
"HAVING Batting.teamID='" & List2.Text & "' " & _
"ORDER BY SUM(Batting.G) DESC"

I wanted to adjust the Batting.teamID='" & List2.Text & "' part. Instead of matching a single Batting.teamID, I wanted to look for several. Some franchises have different Batting.teamID - like the Angels have ANA, CAL and LAA. I can find these using:

SELECT DISTINCT teamID FROM Teams WHERE franchID ='ANA'

I sort of have it working as I've used:

"HAVING Batting.teamID IN (" & _
"SELECT DISTINCT teamID From Teams WHERE franchID='" & List2.Text & "') " & _
"ORDER BY SUM(Batting.HR) DESC"

but I just checked one and found Hank Aaron listed twice among the Braves leaders - once for Milwaukee and once for Atlanta. He should appear once with the totals combined.

The only team/franchise ID in the Batting table is teamID. How do I group Aaron with Batting.teamID's of ML1 and ATL to be part of the franchise leaders of the Braves that have a Teams.franchID of ATL and Teams.teamID's of ATL, BSN and ML1?

[Antmeister]

Originally Posted by 21C

Okay, I don't know if I could help you all that much since I don't know what the purpose of franchID is, but couldn't you just get the totals by doing this.

SELECT DISTINCT teamID FROM Teams WHERE franchID = 'ANA' AND franchID = 'CAL' AND franchID = 'LAA'

Then just leave the rest of the code from the top example alone and try and see if this works. Hopefully my syntax isn't off.

[21C]

Teams.franchID has the franchise info ( like ANA ) and Teams.teamID has the team info ( like ANA, CAL and LAA ).

yearID lgID teamID franchID
1993 AL CAL ANA
1999 AL ANA ANA

Using this:

SELECT DISTINCT teamID FROM Teams WHERE franchID='" & List2.Text & "'"

Gives me the different teamID's which I want to be able to use with teams like the Braves, Dodgers, Brewers and any other teams that moved cities ( or changed names or changed leagues ) but retained their franchise status.

Your suggestion didn't quite work since the franchID's are all ANA - the teamID's are different.

I'm thinking that the problem lies with the GROUP BY portion. It's grouping the stats by Batting.teamID. It is putting the correct franchise information together but not grouping by Teams.franchID. I may have to make some alterations with the JOIN.

Thanks for taking a look.

[Telle]

You're selecting and grouping by the teamId, but an individual player may be joined to multiple teamIds.. so of course you're going to get mutliple listings. If your eally want everything selected and grouped by franchId, then use that instead. It looks like you'll need to add in another join to the team table to get the franchId.