Need to grab one field from a third table on a already working sql query

A EE Pro helped me out creating a functional two table query. I need to a use one field from a third table
the table is called NFLPassPlayInfo and the field is called season which would be the year. Just curious I just need the year to match playerid once not sixteen times. Cause in NFLPassPlayInfo a Playerid will come up at least 16 times cause of sixteen games in Nfl and if the team makes it into the playoffs even more. But maybe this is non issue I'm just wondering I got this for the SQL Statement If this sounds confusing let me know.Just need NFLPassPlayInfo and the column is called season just need it to match playerID tHANKS ee

sql = "Select FirstName,LastName,np.PlayerID,TeamID,Seasontype,Conference,Att,Comp,Yards,Long,TD,Inter,Sacks,SKYds,Rating FROM NflPlayers np"
        sql += " join NFLPlayersPass npp on np.PlayerID = npp.PlayerID"
        sql += " WHERE np.PlayerID= @PlayerID"

Open in new window

Tom PowersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I can't see the relationship between NFLPassPlayInfo.season and the other tables.

To get the same item just once, use MIN() or MAX().
Kyle AbrahamsSenior .Net DeveloperCommented:
Hi Tom,

Can you clarify your question a little bit?

The NFLPassPlayInfo holds all games for a player I'm assuming.  So even if you filter on a year you're still going to get all those games no?

If you have sql server management studio or post a list of the columns that would help tremendously.
Brian CroweDatabase AdministratorCommented:
It would be helpful if you could post the schema definitions for the tables you mention.  If the database you are using is at all normalized then it seems odd that you would have to pull something as generic as Season from the PassPlayInfo table.  If I am interpreting it correctly then PassPlayInfo should join to a single game and the game would join to a single season which would eliminate your multiple returns.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Tom PowersAuthor Commented:
Kyle,
NFLPLAYERS has name and some other attributes NFLPlayersPass is just a summary of the whole year for the QB for example and I just realized that when it was designed the developer left out season the only table that I see is a table called NFLPASSPLAYINFO but that table is for everygame stats not yearly like I need so this won't work right? Any way here is a screen shot and app screen shot
THIS IS NFLPASSPLAYINFO THIRD TABLE
SQL EXPRESS
HERE IS APP WHICH I WOULD LOVE TO INSERT SEASON AKA YEAR

APP SCREEN SHOT
i DON'T THINK THIS CAN BE DONE BUT BETTER GET A 2ND OPINION RECREATE TABLE WITH SEASON ADDED BUT THAT WILL BE A BITCH NOT CAUSE IT'S HARD THE CODE WILL HAVE TO BE ALTERED IN SEVERAL PLACES. 2ND OPINION
Tom PowersAuthor Commented:
IN CODE YOU WROTE i MISTAKENLY HAD SEASON BUT IT DOESN'T EXISTS IN FIRST TWO TABLES ONCE I REMOVED IT CODE WORKED BUT I KNOW THE BOSSES ARE GOING TO WANT TOM SEE A YEAR FOR EACH ROW IN APP.
Tom PowersAuthor Commented:
OK HERE IS DATABASE SCHEMA HOPE THIS IS RIGHT CAUSE I NOT 100% SURE WHAT THAT MEANS BUT
HERE SCREENS SHOTS
Schema
Tom PowersAuthor Commented:
Need anything just ask.
Tom PowersAuthor Commented:
Maybe a View but I have no Idea how to I have a book about sql hoping I can find something
Brian CroweDatabase AdministratorCommented:
I noticed there is a GameID column in the NFLPlayPassInfo table which implies that there is an NFLGame table somewhere in the DB.  I assume that there is a Season column in the Game table.

Try the query below (hopefully I guessed right on the column source since you don't have them prepended with the table)

Select P.FirstName,
   P.LastName,
   P.PlayerID,
   P.TeamID,
   P.Seasontype,
   G.Season,               --assuming the name of the column
   P.Conference,
   PP.Att,
   PP.Comp,
   PP.Yards,
   PP.Long,
   PP.TD,
   PP.Inter,
   PP.Sacks,
   PP.SKYds,
   PP.Rating
FROM NflPlayers AS P
INNER JOIN NFLPlayersPass  AS PP
   ON P.PlayerID = PP.PlayerID
INNER JOIN NFLGame AS G     --assuming the name of the table here
   ON PP.GameID = G.GameID
WHERE P.PlayerID= @PlayerID
Tom PowersAuthor Commented:
Yo Brian This works in SQL EXPRESS it grabs everything like every game I need just bthe total stats with season AKA Year But I take nothing against you what you wrote was awesome and I may use once I can get it debugged in VB Not you I just did this
 Public Sub NflInnerjoin()
        Dim sql As String


        Dim SelectCommand As New SqlCommand
        dvgPass.DataSource = Nothing
        dvgPass.ClearSelection()
        Dim ds As New DataSet
        connetionString = "Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=sportsrus"
        connection = New SqlConnection(connetionString)

        sql = "Select P.FirstName,P.LastName,P.PlayerID,P.TeamID, PP.Seasontype, G.Season,PP.Conference,PP.Att,PP.Comp,"
        sql += "PP.Yards,PP.Long, PP.TD, PP.Inter,PP.Sacks, PP.SKYds, PP.Rating() FROM NflPlayers AS P"
        sql += "INNER JOIN NFLPlayersPass  AS PP ON P.PlayerID = PP.PlayerID INNER JOIN NFLPassPlayInfo AS G ON P.TeamID = G.TeamID "
        sql += "WHERE P.PlayerID= @PlayerID"

        Try
            connection.Open()
            adapter = New SqlDataAdapter(sql, connection)
            adapter.SelectCommand.Parameters.AddWithValue("@PlayerID", PlayerID2)
            adapter.Fill(ds)


            connection.Close()
            dvgPass.DataSource = ds.Tables(0)

        Catch ex As SqlException
            MsgBox(ex.ToString)
        End Try

        ' ds.Tables(Currentrow) = "Lastname" = txtLname.Text
        ' ds.Tables(Currentrow).Column("College") = txtCollege.Text

        ' txtLname.Text = ds.Tables(Currentrow).TableName("Lastname")
        ' txtCollege.Text = ds.Tables(Currentrow).Item("College")

        Me.dvgPass.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.dvgPass.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        dvgPass.ColumnHeadersHeight = 55
        dvgPass.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
        ' Add the image column to the grid.
        ' DataGridView1.Columns.Add(imageCol)

        '  DataGridView1.Columns("Lastname").Frozen = False
        ' DataGridView1.Columns("Lastname").Frozen = True



        ' dvgPass.DataSource = Nothing
        ' dvgPass.ClearSelection()




        ' ds.Tables(Currentrow) = "Lastname" = txtLname.Text
        ' ds.Tables(Currentrow).Column("College") = txtCollege.Text

        ' txtLname.Text = ds.Tables(Currentrow).TableName("Lastname")
        ' txtCollege.Text = ds.Tables(Currentrow).Item("College")

        Me.dvgPass.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.dvgPass.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        dvgPass.ColumnHeadersHeight = 55
        dvgPass.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
        ' Add the image column to the grid.
        ' DataGridView1.Columns.Add(imageCol)

        'dvgPass.Columns("Lastname").Frozen = True

       
    End Sub

Open in new window

got this error message in vb again it's me your sql thought me a few things
VB SQL ERROR MESSAGE
Kyle AbrahamsSenior .Net DeveloperCommented:
spaces again:

sql = "Select P.FirstName,P.LastName,P.PlayerID,P.TeamID, PP.Seasontype, G.Season,PP.Conference,PP.Att,PP.Comp,"
        sql += " PP.Yards,PP.Long, PP.TD, PP.Inter,PP.Sacks, PP.SKYds, PP.Rating() FROM NflPlayers AS P"
        sql += " INNER JOIN NFLPlayersPass  AS PP ON P.PlayerID = PP.PlayerID INNER JOIN NFLPassPlayInfo AS G ON P.TeamID = G.TeamID "
        sql += " WHERE P.PlayerID= @PlayerID"

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian CroweDatabase AdministratorCommented:
Assuming you want to group by player and season...

Select P.FirstName,
   P.LastName,
   P.PlayerID,
   P.TeamID,
   P.Seasontype,
   G.Season,               --assuming the name of the column
   P.Conference,
   SUM(PP.Att) AS Att,
   SUM(PP.Comp) AS Comp,
   SUM(PP.Yards) AS Yards,
   MAX(PP.Long) AS Long,
   SUM(PP.TD) AS TD,
   SUM(PP.Inter) AS Inter,
   SUM(PP.Sacks) AS Sacks,
   SUM(PP.SKYds) AS SKYds,
   AVG(PP.Rating) AS Rating
FROM NflPlayers AS P
INNER JOIN NFLPlayersPass  AS PP
   ON P.PlayerID = PP.PlayerID
INNER JOIN NFLGame AS G     --assuming the name of the table here
   ON PP.GameID = G.GameID
WHERE P.PlayerID= @PlayerID
GROUP BY P.FirstName,
   P.LastName,
   P.PlayerID,
   P.TeamID,
   P.Seasontype,
   G.Season,
   P.Conference
ORDER BY G.Season DESC

Open in new window

Tom PowersAuthor Commented:
Guys thanks alot I don't know where I'd be without people like you guys I wish I could buy your beer. I posted another one it's a little different believe I got a book open on TSQL Stored Procedures and I feel like I'm learning more from u guys.Thanks again
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.