Solved

LINQ Query Joining same table twice

Posted on 2013-10-25
4
1,282 Views
Last Modified: 2016-02-10
I am having trouble with a LINQ query to return the name of the Home and Away team from the same table.  I have  schedule table with awayid and homeid.  In sql server it's easy to reference the teams table as teams and teams1 and get my data in same record.  I cannot figure out the LINQ code to produce same results.  

This brings in home team name.  How would I change it to also get away team from teams table.

 Dim curScores = From s In db.Schedules Join hteams In db.Teams On s.HomeID Equals hteams.Teamid
                Where s.Week = 3 And s.Season = "2014" Select Teamid = s.HomeID, HomeTeam = hteams.TeamName
0
Comment
Question by:Break40
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 500 total points
ID: 39602038
You need to do something like:

From
      s In db.Schedules
      Join t1 hteams In db.Teams
            On s.HomeID Equals t1.Teamid
      Join t2 hteams In db.Teams
            On s.HomeID Equals t2.Teamid
        Where s.Week = 3 And s.Season = "2014" Select Teamid = s.HomeID, HomeTeam = t1.TeamName
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39602145
If I understand the issue correctly, then one of Zberteoc's Join statements needs to reference the s.AwayID rather than both pointing to s.HomeID.
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39602545
I think Kaufned is correct. The query should be:

From
      s In db.Schedules
      Join t1 hteams In db.Teams
            On s.HomeID Equals t1.Teamid
      Join t2 hteams In db.Teams
            On sAwayID Equals t2.Teamid
Where
      s.Week = 3
      And s.Season = "2014"
Select
      HomeTeamId       =s.HomeID,
      HomeTeam       =t1.TeamName,
      AwayTeamId      =s.AwayId,
      AwayTeam      =t1.TeamName
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39602992
One correction the last line should be:

AwayTeam      =t2.TeamName
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

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…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

724 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