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....
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....
Visual Basic ClassicMicrosoft AccessMicrosoft SQL Server
Last Comment
jazjef
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
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.
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