Solved

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

Posted on 2014-10-02
13
97 Views
Last Modified: 2014-10-02
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

0
Comment
Question by:powerztom
  • 7
  • 3
  • 2
  • +1
13 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40357123
I can't see the relationship between NFLPassPlayInfo.season and the other tables.

To get the same item just once, use MIN() or MAX().
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 40357154
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.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40357187
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.
0
 

Author Comment

by:powerztom
ID: 40357281
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
0
 

Author Comment

by:powerztom
ID: 40357291
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.
0
 

Author Comment

by:powerztom
ID: 40357475
OK HERE IS DATABASE SCHEMA HOPE THIS IS RIGHT CAUSE I NOT 100% SURE WHAT THAT MEANS BUT
HERE SCREENS SHOTS
Schema
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:powerztom
ID: 40357479
Need anything just ask.
0
 

Author Comment

by:powerztom
ID: 40357561
Maybe a View but I have no Idea how to I have a book about sql hoping I can find something
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40357602
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
0
 

Author Comment

by:powerztom
ID: 40357892
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
0
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 250 total points
ID: 40357968
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

0
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 250 total points
ID: 40358018
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

0
 

Author Closing Comment

by:powerztom
ID: 40358086
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
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now