Link to home
Create AccountLog in
Avatar of jazjef
jazjef

asked on

How do I add an additional INNER JOIN to a complex SQL statement?

I have a long SQL statement in VB6 that pulls data from a couple of MS Access tables to calculate some baseball statistics.

QUESTION:
How do I add another INNER JOIN to the SQL statement? I already have one INNER JOIN integrated already, but I can't seem to get the second one---I get syntax errors all the time so I'm not getting the punctuation etc correct.


My functioning statement that simply adds the blank columns and their field names is seen here:

SqlString = "SELECT (Master.nameFirst + '_' + Master.nameLast) AS Player, (Batting.yearID & Batting.teamID) AS Team, Fielding.POS AS POS, Batting.G, AB, R, H, [2B], [3B], HR, RBI, (Master.bats) AS BAT, BB, SO, Batting.SB, Batting.CS, ((H + BB + HBP) / (AB + BB + HBP + SF)) AS OBP, ('') AS vs_L, ('') AS vs_R, ((Fielding.PO + Fielding.A) / (Fielding.PO + Fielding.A + Fielding.E)) AS FPCT FROM (Batting INNER JOIN Master ON Batting.playerID = Master.playerID) INNER JOIN Fielding ON Batting.playerID = Fielding.playerID WHERE Fielding.A > 0 AND AB > 0 AND Batting.yearID = " & Text12.Text & " AND Batting.teamID = " & "'" & Text13.Text & "'" & " ORDER BY Batting.G DESC"



I'm trying to INNER JOIN two more fields from another table called 'BatSplits'. My functioning statement creates two blank columns called 'vs_R' and 'vs_L' [see above]. I have changed my statement to include the table these columns come from----here are the statements I've added:

(BatSplits.vs_LHP) AS vs_L, (BatSplits.vs_RHP) AS vs_R



Here's what I have so far.... it doesn't work because the INNER JOIN part is not integrated yet:

SqlString = "SELECT (Master.nameFirst + '_' + Master.nameLast) AS Player, (Batting.yearID & Batting.teamID) AS Team, Fielding.POS AS POS, Batting.G, AB, R, H, [2B], [3B], HR, RBI, (Master.bats) AS BAT, BB, SO, Batting.SB, Batting.CS, ((H + BB + HBP) / (AB + BB + HBP + SF)) AS OBP, (BatSplits.vs_LHP) AS vs_L, (BatSplits.vs_RHP) AS vs_R, ((Fielding.PO + Fielding.A) / (Fielding.PO + Fielding.A + Fielding.E)) AS FPCT FROM (Batting INNER JOIN Master ON Batting.playerID = Master.playerID) INNER JOIN Fielding ON Batting.playerID = Fielding.playerID WHERE Fielding.A > 0 AND AB > 0 AND Batting.yearID = " & Text12.Text & " AND Batting.teamID = " & "'" & Text13.Text & "'" & " ORDER BY Batting.G DESC"


Many thanks for any assistance....
ASKER CERTIFIED SOLUTION
Avatar of William Nettmann
William Nettmann
Flag of South Africa image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of jazjef
jazjef

ASKER

I'm going to have to kill off this question folks...... I found the problem, and it's not a syntax issue. The problem is that the two tables have what I thought was a common ID field between them. I turns out that since they are from different databases, they appear similar as fields, but the last 3-4 characters of the ID differ between the fields in the two tables. The fields are so similar that I never noticed it before---I only noticed it when I actually got the syntax correct, and it returned ZERO lines of data. I thought that was weird, so I checked the ID columns and that's where I noticed the discrepancy.

I'm going to be doing a MS Access INSERT FIELD on one of the tables to solve the problem.  I can reference both/either ID field once they're in the same table.

Be on the lookout for an MS Access INSERT FIELD question I'll be asking if you're interested. Many thanks
Avatar of PatHartman
Creating multiple questions on the same topic will not get you help faster and will certainly annoy the people who are wasting their time trying to help you.
Avatar of jazjef

ASKER

I couldn't figure out how to change categories for the question, so I created a modified question under a couple of different categories. I was pretty sure that EE would see the duplication and might remove it...but they didn't.

I'm not sure what it is about the tone of your comments that you think makes me want to give you points for assisting me in any way. Here's some wisdom for you PatHartman---you'll apply it if you want to maximize your points on EE: "You get more flies with honey than you do with vinegar"

Please leave me and my questions alone in the future. I don't want assistance from angry unstable people.

Thank you.