Solved

quaduplites records in datagridview from a 3 join sql query

Posted on 2014-10-20
8
35 Views
Last Modified: 2016-08-02
Hey I wrote a query where I join 3 tables I'm getting same player 4 times I have no Idea why. here is sql queies
 sql = "SELECT np.FirstName, np.LastName, np.Position,Teams.Fullname, np.College,np.Status,np.Jersey, np.Height,"
        sql += " np.Weight, np.Born, np.BioHtml, np.HighSchool,np.TeamID, np.PlayerID, np.NCAAID, Draft.draftyear, Draft.round,Draft.DraftPos, Draft.OverallPos, Draft.DraftTeamID, Draft.OrigTeamID"
        sql += " FROM NFLPlayers np Join Teams ON Teams.TeamID = np.TeamID Join Draft ON Draft.PlayerID = np.NCAAID WHERE"
        sql += " Status ='A' AND Position<> ''"
       

        connection = New SqlConnection(connetionString)

        Try
            connection.Open()
            adapter = New SqlDataAdapter(sql, connection)
            adapter.Fill(ds)
            connection.Close()
            DataGridView1.DataSource = ds.Tables(0)

Open in new window


here is what is outputted
APP LOOK
What am I doing wrong? EE Please help
0
Comment
Question by:powerztom
  • 2
  • 2
8 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40392549
Eyeballeth the JOIN ... ON columns between the two tables, as 'quadruples' indicates there may be more than one place where either (1) there is a one-to-many table and you're not uniquely defining the 'many' row to grab, so it returns many, or (2)  there is another ON column that's missing and you need to identify
0
 

Author Comment

by:powerztom
ID: 40397341
I've requested that this question be deleted for the following reason:

I figured out that I didn't need to use 3 join query.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40397342
Please spell out in greater detail how this was 'figured out', so we can accept that answer so others may benefit.
Cancelling delete request.
0
 

Assisted Solution

by:powerztom
powerztom earned 0 total points
ID: 40397400
Well I made the mistake of thinking the NFLPlayers table had a field called NCAAID and I thought that that field could join a field in the Draft table called PlayerID. WELL they don't match cause PlayerID is NFL AND NCAAID was college. But in NFLPLAYERS Table has a field called PlayerID That matches raft Table's PlayerID FIELD. aND THAT IS THE Connection that matches. I should have saw this from the beginning.Lesson don't assume and rush without taking a good look at your situation.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

776 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