Solved

Ambigious column call on my sql load statement 3 table join help

Posted on 2014-10-02
5
171 Views
Last Modified: 2014-10-06
I've gotten some many errors I am just going to explain this SQL Problem I'm good at VB BUT NOVICE AT SQL that's why I post well this project I have to use report viewer but I'm not worrying about that OK HERE IT IS i WOULD LIKE TO ON FORM LOAD columns from a table called Draft and first and lastname from a table called Nflplayers and a third table named Teams and grab a column called Fullname now the challenge you can join nflplayers field called PlayerID, WITH Draft 's PlayerID BUT the Teams table only has Teamid with NFLPLAYERS COLUMN TeamID IN COMMON. Can a join like this be done.I tried have a look

Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
Public Class Form1
    Public Sportcode As String
    Dim TableName As String
    Public roundnumbers As Integer
    Public connetionString As String
    Dim connection As SqlConnection
    Dim adapter As SqlDataAdapter
    Dim PlayerID2 As Integer

    Public Sub GetNflRound()
        Dim sql As String
        Dim ds As New DataSet
        ' Dim mySQLDATAAdapter As New SqlDataAdapter
        Dim SelectCommand As New SqlCommand
        ' Dim da As New SqlDataAdapter
        connetionString = "Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=sportsrus"
        connection = New SqlConnection(connetionString)

        sql = "Select FirstName,LastName,np.PlayerID,np.TeamID,Draft.draftyear,Draft.round,Draft.Draftpos,Draft.OverAllPos,Draft.DraftteamID,Draft.OrigTeamID,Draft.Sportcode FROM NflPlayers np"
        sql += " join Draft on np.PlayerID = Draft.PlayerID"
        sql += " WHERE Draft.round AND Draft.draftyear AND Draft.sportcode Order By Draft.draftyear desc"

       

        Try
            connection.Open()
            adapter = New SqlDataAdapter(sql, connection)
            adapter.SelectCommand.Parameters.AddWithValue("@round", roundnumbers)
            adapter.SelectCommand.Parameters.AddWithValue("@draftyear", tscmbyear.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@sportcode", "AC")


            adapter.Fill(ds)


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

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

        
        Me.dvgDraft.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.dvgDraft.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        dvgDraft.ColumnHeadersHeight = 55
        dvgDraft.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
       

        Me.dvgDraft.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.dvgDraft.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        dvgDraft.ColumnHeadersHeight = 55
        dvgDraft.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
       

        dvgDraft.Columns("Lastname").Frozen = True

    End Sub
    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
        Application.Exit()
    End Sub

    Private Sub ExitToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExitToolStripMenuItem.Click
        Application.Exit()
    End Sub

    Private Sub ToolStripButton2_Click(sender As Object, e As EventArgs) Handles ToolStripButton2.Click
        If WebBrowser1.Visible = True Then
            WebBrowser1.Visible = False
            Exit Sub
        End If
        If WebBrowser1.Visible = False Then
            WebBrowser1.Visible = True
            Exit Sub
        End If



    End Sub
    Public Sub roundtonumbers()
        If tscmbRound.Text = "None" Then
            roundnumbers = 0
            Exit Sub
        End If
        If tscmbRound.Text = "One" Then
            roundnumbers = 1
            Exit Sub
        End If
        If tscmbRound.Text = "Two" Then
            roundnumbers = 2
            Exit Sub
        End If
        If tscmbRound.Text = "Three" Then
            roundnumbers = 3
            Exit Sub
        End If
        If tscmbRound.Text = "Four" Then
            roundnumbers = 4
            Exit Sub
        End If
        If tscmbRound.Text = "Five" Then
            roundnumbers = 5
            Exit Sub
        End If
        If tscmbRound.Text = "Six" Then
            roundnumbers = 6
            Exit Sub
        End If
        If tscmbRound.Text = "Seven" Then
            roundnumbers = 7
            Exit Sub
        End If
        If tscmbRound.Text = "Eight" Then
            roundnumbers = 8
            Exit Sub
        End If
        If tscmbRound.Text = "Nine" Then
            roundnumbers = 9
            Exit Sub
        End If










    End Sub

    Public Sub Initialize()
        Dim connetionString As String
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim sSQL As String
        Dim DS As New DataSet
        If Sportcode = "AB" Then
            TableName = "NFLPlayers"
        End If
        If Sportcode = "AC" Then
            TableName = "NBAPlayers"
        End If
        If Sportcode = "AD" Then
            TableName = "NHLPlayers"
        End If


        connetionString = "Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=sportsrus"

        connection = New SqlConnection(connetionString)
        ' txtsql.Text = ""

        Try
            connection.Open()
            adapter = New SqlDataAdapter(sSQL, connection)
            adapter.Fill(DS)
            connection.Close()
            dvgDraft.DataSource = DS.Tables(0)
        Catch ex As SqlException
            MsgBox(ex.ToString)
            Me.dvgDraft.RowsDefaultCellStyle.BackColor = Color.Bisque
            Me.dvgDraft.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
            dvgDraft.ColumnHeadersHeight = 55
            dvgDraft.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
            ' Add the image column to the grid.
            ' DataGridView1.Columns.Add(imageCol)
        End Try
        '  DataGridView1.Columns("Lastname").Frozen = False
        '  dvgDraft.Columns("Lastname").Frozen = True


    End Sub
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        WebBrowser1.Navigate("HTTP://SPORTSNETWORK.COM/TEAMS.ASP")
        '2 GetNflRound()
    End Sub
    Public Sub Comboxquery()
        If tscmbRound.Text = "" Or tscmbsport.Text = "" Or tscmbyear.Text = "" Then
            MessageBox.Show("Select all three categories Stupid")
            Exit Sub
        End If
        If tscmbRound.SelectedText <> "" Or tscmbsport.SelectedText <> "" Or tscmbyear.SelectedText <> "" Then
            '  GetNflRound()

        End If
    End Sub
    Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) Handles ToolStripButton1.Click
        Comboxquery()

    End Sub

    Dim time As DateTime = DateTime.Now

    Dim s As String = time.Year.ToString("yyyy")
   
    Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
        Dim time As DateTime = DateTime.Now

        Dim s As String = time.Year.ToString()
        TextBox1.Text = s
    End Sub
End Class

Open in new window

how in God'S NAME DO YOU Join 3 tables when 2 have a common value PlayerID and then tables have teamID AND MAKE THEM WORK i HAVE DROP DOWN SO USER CAN VIEW DRAFTS FROM DIFFERENT YEARS
cHECKOUT SCREENSHOT
OPTION TO CUSTOM SQL QUERY
2ND oPTION TO CREATE Custom sql query
3rd Option in creating customized
0
Comment
Question by:powerztom
  • 3
  • 2
5 Comments
 

Author Comment

by:powerztom
ID: 40357776
I got just Draft Table to load but it just loads it doesn't accept the parameters.
If anyone can get Player First and LastName from NFLPlayers Tables have a inner join on Table called Teams  join on TeamID AND DISPLAY COLUMN fULLNAME THAT WOULD BE THE ULTIMATE. tHANKS ee

draft table loaded
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40358718
>>"how in God'S NAME DO YOU Join 3 tables when 2 have a common value PlayerID and then tables have teamID AND MAKE THEM WORK"?

well, you concentrate on the tables
not on screen layouts
or VB or .NET or ASP or Java or C# or Ruby/R/blah

what ARE the tables?
what ARE the fields in each table?
and some sample data from each table?

This is the only relevant information I could get from your code:
        sql = "Select FirstName,LastName,np.PlayerID,np.TeamID,Draft.draftyear,Draft.round,Draft.Draftpos,Draft.OverAllPos,Draft.DraftteamID,Draft.OrigTeamID,Draft.Sportcode FROM NflPlayers np"
        sql += " join Draft on np.PlayerID = Draft.PlayerID"
        sql += " WHERE Draft.round AND Draft.draftyear AND Draft.sportcode Order By Draft.draftyear desc"

Open in new window


When formatted more my taste that query looks like this:

SELECT
      FirstName    , LastName    , np.PlayerID    , np.TeamID    , Draft.draftyear    , Draft.round
    , Draft.Draftpos    , Draft.OverAllPos    , Draft.DraftteamID    , Draft.OrigTeamID    , Draft.Sportcode
FROM NflPlayers np
      JOIN Draft
                  ON np.PlayerID = Draft.PlayerID
WHERE Draft.round
      AND Draft.draftyear
      AND Draft.sportcode
ORDER BY
      Draft.draftyear DESC

Open in new window

I only see 2 tables listed and a few fields from those 2 tables, and a where clause that looks invalid.

Are you referring to this?
       If Sportcode = "AB" Then
            TableName = "NFLPlayers"
        End If
        If Sportcode = "AC" Then
            TableName = "NBAPlayers"
        End If
        If Sportcode = "AD" Then
            TableName = "NHLPlayers"

Open in new window

Could you rethink the question details and try to reveal more about the underlying data model, otherwise I don't see how I can help you join tables I know so very little about.
0
 

Author Comment

by:powerztom
ID: 40359474
My Company we deal with sports the developer left and I'm finding alot of fields that would make this upgrade alot easier but I IN THIS FORM HAVE TOM LIST DRAFT BASED ON 3 PARAMETER OR THREE DROPDOWNS THAT WILL SHAPE THE SQL QUERY ONE FOR ROUND DARFTED ANOTHER PLAYERS NAME AND FINALLY THE SPORT WHICH HA S A CODE TO REPRESENT nfl,nhl and nba. However there is a table called teams that has a column called fullname example Philadelphia Eagles that's what AI need for reportviewer here is a query written in vb6 syntax hopes this helps cause I'm good at simple queries. But
select Label from Teams where teamID= OrigteamID THIS IS THE THIRD JOIN BETWEEN TABLE DRAFT AND DRAFT. aNY qUSETIONS HOLLAR tHANKS MAN
Public Sub Initialize()
If Sportcode = SPORT_CODE_NFL Then
TableName = "NFLPlayers"
End If
If Sportcode = SPORT_CODE_NBA Then
TableName = "NBAPlayers"
End If
If Sportcode = SPORT_CODE_NHL Then
TableName = "NHLPlayers"
End If
sSQL = "SELECT Round,OverallPos,DraftYear,DraftPos,(Select Label from Teams where teamid = Draft.DraftTeamID) " & _
"as DraftLabel,(select Label from Teams where teamID= OrigteamID) as OrigLabel,DraftTeamID,OrigTeamID,PlayerID,PK" & _
",isnull((SELECT Lastname+', '+Firstname from " & TableName & " where PlayerID = Draft.PlayerID),'') as Name FROM Draft WHERE " & _
"sportcode = '" & Sportcode & "' and DraftYear= '" & Year(Now) & "' ORDER BY ROUND,DraftPos"
Adodc1.ConnectionString = oCon.ConnectionString
Adodc1.CommandType = adCmdText
Adodc1.RecordSource = sSQL
Adodc1.Refresh
 
Set Grid.DataSource = Adodc1
FixGrid
Call Grid_RowColChange
End Sub

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40360537
oh dear, You do like to live dangerously!

sSQL = "SELECT ... ,(Select Label from Teams where teamid = Draft.DraftTeamID) ...

The bit in bold above is a "correlated subquery" the underlined bit is "the correlation".

These subqueries can only supply a single row and a single value; if they don't then your query will fail with an error.
That's problem 1.   If using these in the select clause you should take precautions against this error.

They are also inefficient.
That's problem 2

You have three of these correlated subqueries.
That's problem 2a (just more inefficiency)

Here is the query formatted so I can understand it better:if you know how to correlate you know how to join
Those 3 highlighted lines are how the 3 subqueries "correlate" to the [DRAFT] table (line 24)

If you know how to correlate you know how to join

Truly! all you need to do is re-arrange this query a little, here is how that process starts!
JOIN instead of correlate!
So, a query that JOINS draft to team (as draft team) and team (as orig team) and player isn't so scary; and as you are already "correlating" these tables, take the plunge go ahead and join instead.
SELECT
        Draft.Round
      , Draft.OverallPos
	, Draft.DraftYear
	, Draft.DraftPos
	, DT.Label AS DraftLabel
	, OT.Label AS OrigLabel
	, Draft.DraftTeamID
	, Draft.OrigTeamID
	, Draft.PlayerID
	, Draft.PK
	, isnull((P.Lastname + ', ' + P.Firstname), '') AS NAME
FROM Draft

INNER JOIN Teams AS DT  ON DT.teamid = Draft.DraftTeamID
INNER JOIN Teams AS OT  ON OT.teamID = Draft.OrigteamID
INNER JOIN ??????? AS P ON P.PlayerID = Draft.PlayerID

WHERE Draft.sportcode = &Sportcode
	AND Draft.DraftYear = Year(Now)
ORDER BY
        Draft.ROUND
	, Draft.DraftPos
;

Open in new window


NOW, because we are joining, there may be one last issue to iron out.

I have used "INNER JOINS". Now IF for any reason you can have a record in the draft table that DOES NOT have a matching record in another table, then you  would use a "LEFT JOIN" (or "LEFT OUTER JOIN").

Let's put it this way:
IF Draft.DraftTeamID can be NULL then use a LEFT JOIN
IF Draft.OrigteamID can be NULL then use a LEFT JOIN
IF Draft.PlayerID can be NULL then use a LEFT JOIN

If you are unsure about this, ask.
Here's a nice visual guide to joins

AMBIGUITY

Your question involves ambiguity, so I should mention what one does to avoid it. In the query code block above please note that I have changed almost every line so that every field reference has table.field or alias.field

absolutely every field is referenced this way

That is how you avoid ambiguity.
0
 

Author Closing Comment

by:powerztom
ID: 40363644
Dude I just got assigned another project I have to come back to this But you went above and beyond on this ticket I want you to have points When I come back to this I'll test it Looks good from looking at it and reading your documentation.Thanks again
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

16 Experts available now in Live!

Get 1:1 Help Now