For Bill W. Please

Results 1 to 3 of 3

Thread: For Bill W. Please

  1. #1
    Jason Guest

    Default For Bill W. Please

    Hey Bill,<BR><BR>I posted a similiar question a few days ago, but had no luck. I was hoping you could assist with a Select statement. Im trying to join two tables. Table 1 is called Members. Table 2 is called DoublesMatches.<BR>Members has FirstName, LastName, and MemberID(Primary Key)<BR>DoublesMatches has Winner1ID, Winner2ID, Loser1ID, Loser2ID, MatchID. There is an existing relationship for all four fields in the DoublesMatches to the MemberID field in the Members table. Ive been trying to write a query that will display the first and last name of each of the members in the DoublesMatches table. I can do it one at a time like this:<BR><BR>SELECT [Members].[MemberID], [Members].[FirstName], [Members].[LastName], [DoublesMatches].[MatchID], [DoublesMatches].[Winner1ID], [DoublesMatches].[Winner2ID], [DoublesMatches].[Result], [DoublesMatches].[Loser1ID], [DoublesMatches].[Loser2ID], [DoublesMatches].[MatchDate]<BR>FROM Members INNER JOIN DoublesMatches ON [Members].[MemberID] =[DoublesMatches].[Winner1ID];<BR><BR>How can that one statement be changed so it will display all of the first and last names, and not just the first and last name of Winner1ID?<BR><BR>Any advice would be much appreciated!

  2. #2
    Join Date
    Dec 1969

    Default Not the Master , Not even his apprentice.

    Try This<BR><BR>SELECT Members.MemberID, <BR>Members.FirstName, <BR>Members.LastName, <BR>DoublesMatches.MatchId, <BR>DoublesMatches.Winner1Id, <BR>DoublesMatches.Winner2Id, <BR>DoublesMatches.Result, <BR>DoublesMatches.Loser1ID, <BR>DoublesMatches.MatchDate, <BR>Members_1.FirstName AS WnningPartnerFirstName,<BR>Members_1.LastName AS WinningPartnerLastName, <BR>Members_2.FirstName AS Loser1FirstName, <BR>Members_2.LastName AS Loser1LastName, <BR>Members_3.FirstName AS Loser2FirstName, <BR>Members_3.LastName AS Loser2LastName<BR>FROM (((Members INNER JOIN DoublesMatches ON Members.MemberID = DoublesMatches.Winner1Id) <BR>INNER JOIN Members AS Members_1 ON DoublesMatches.Winner2Id = Members_1.MemberID) INNER JOIN Members AS Members_2 ON DoublesMatches.Loser1ID = Members_2.MemberID) <BR>INNER JOIN Members AS Members_3 ON DoublesMatches.Loser2Id = Members_3.MemberID;<BR><BR><BR><BR>Note that I have spaced this out for readability<BR><BR>R

  3. #3
    Jason Guest

    Default Thank you!!!!!!!!!!

    Works great. Thanks so much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts