SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2002
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL: Selecting the same field 2x

    The scenario:

    This is a continuation of my game ladder. What I am doing now is displaying all the battles that have been submitted over a period of time. I have two user id's stored in the table, and the user's names stored in another table, like so:

    TABLE Grudge:
    Grudge_ID
    Grudge_Pilot1
    Grudge_Pilot2

    TABLE Luser:
    Luser_ID
    Luser_LuserName

    I need to get the names of each user for each record, so I can display both names. Currently I am using this query:

    SELECT Grudge.Grudge_ID,Grudge.Grudge_Pilot1,Grudge.Grudge_Pilot2,Luser.Luser_LuserName,Grudge.Grudge_Date FROM Grudge,Luser WHERE Grudge.Grudge_Winner=0 AND (Luser.Luser_ID=Grudge.Grudge_Pilot2 OR Luser.Luser_ID=Grudge.Grudge_Pilot1) ORDER BY Grudge.Grudge_ID

    Which faithfully returns:
    Code:
    Grudge_ID Grudge_Pilot1 Grudge_Pilot2 Luser_LuserName Grudge_Date 
    1 2 3 Sting Ray  9/9/2002 
    1 2 3 Blitzkrieg 9/9/2002 
    2 5 9 Ferno      9/10/2002 
    2 5 9 Scratch    9/10/2002
    All well and good, except the fact that it is duplicating records. I could always say "who cares" and simply run with this *BUT* the names aren't always in order with the ID's.

    For example:
    Code:
    Grudge_ID Grudge_Pilot1 Grudge_Pilot2 Luser_LuserName Grudge_Date 
    1 2 3 Sting Ray  9/9/2002 
    1 2 3 Blitzkrieg 9/9/2002
    2 is Blitzkrieg's ID, and 3 is Sting Ray's

    Code:
    Grudge_ID Grudge_Pilot1 Grudge_Pilot2 Luser_LuserName Grudge_Date 
    2 5 9 Ferno      9/10/2002 
    2 5 9 Scratch    9/10/2002
    Here, 5 is Ferno's ID, and 9 is Scratch's. Reverse order from the first 2 records!

    Is there a way I can use 'AS' to create this RS?:
    Code:
    Grudge_ID Grudge_Pilot1 Grudge_Pilot2 Luser_LuserName(1) Luser_LuserName(2) Grudge_Date 
    1 2 3 Blitzkrieg Sting Ray  9/9/2002 
    2 5 9 Ferno      Scratch    9/10/2002
    Notice that I arrainged the names in order of the Pilot ID's..is this possible?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    here's the basic query to join each match to two pilots on the same row
    Code:
    select Grudge.Grudge_ID
         , Grudge.Grudge_Date 
         , Grudge.Grudge_Pilot1
         , P1.Luser_LuserName
         , Grudge.Grudge_Pilot2
         , P2.Luser_LuserName
      from Grudge
    inner
      join Luser P1
        on Grudge.Grudge_Pilot1 = P1.Luser_ID
    inner
      join Luser P2
        on Grudge.Grudge_Pilot2 = P2.Luser_ID
     where Grudge.Grudge_Winner=0
    order
        by Grudge.Grudge_ID
    dunno what Grudge_Winner=0 does, but i left it in
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2002
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Grudge_Winner is the filter I'm using to not select matches that havn't yet been completed.

    select Grudge.Grudge_ID,Grudge.Grudge_Date,Grudge.Grudge_Pilot1,P1.Luser_LuserName,Grudge.Grudge_Pilot2,P2.Luser_LuserName from Grudge inner join Luser P1 on Grudge.Grudge_Pilot1 = P1.Luser_ID inner join Luser P2 on Grudge.Grudge_Pilot2 = P2.Luser_ID where Grudge.Grudge_Winner=0 order by Grudge.Grudge_ID

    Lemme guess and say Access can't do this? I don't have this on SQLServer (yet).

    It look like what I want tho'!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    of course access can do it -- it's a real database

    heck, even mysql can do this one

  5. #5
    SitePoint Enthusiast
    Join Date
    Jan 2002
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just askin', 'cause I get this error:


    OpenRS(select Grudge.Grudge_ID,Grudge.Grudge_Date,Grudge.Grudge_Pilot1,P1.Luser_LuserName,Grudge.Grudge_Pilot2,P2.Luser_LuserName from Grudge inner join Luser P1 on Grudge.Grudge_Pilot1 = P1.Luser_ID inner join Luser P2 on Grudge.Grudge_Pilot2 = P2.Luser_ID where Grudge.Grudge_Winner=0 order by Grudge.Grudge_ID )

    Microsoft JET Database Engine error '80040e14'

    Syntax error (missing operator) in query expression 'Grudge.Grudge_Pilot1 = P1.Luser_ID inner join Luser P2 on Grudge.Grudge_Pilot2 = P2.Luser_ID'.

    Am I missing a comma somwhere?
    Last edited by Metallisoft; Sep 13, 2002 at 08:04.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, access needs parentheses, other database barf on them, i keep forgetting which is which

    select ...
    from ( Grudge
    inner join Luser P1
    on Grudge.Grudge_Pilot1 = P1.Luser_ID
    ) inner join Luser P2
    on Grudge.Grudge_Pilot2 = P2.Luser_ID
    where ...

    rudy

  7. #7
    SitePoint Enthusiast
    Join Date
    Jan 2002
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    <sufer hand signal>You rock!</surfer hand signal>


Bookmarks

Posting Permissions

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