SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: join 2 times

  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    join 2 times

    Code:
    myTable
    (1) 3 6
    (2) 8 4
    (3) 7 2
    (4) 5 1
    
    myTable1
    (1) Mary
    (2) Jane
    (3) Tom
    (4) Carol
    (5) Jack
    (6) Judy
    (7) Rudy
    (8) Chris
    I have 2 tables like the above and I like to produce my target result like the below.
    Code:
    target result
    (1) Tom    Judy
    (2) Chris  Carol
    (3) Rudy   Jane
    (4) Jack   Mary
    The code below is one of steps toward the target result.
    Code:
    on the way code
    
    select myTable.id,name from myTable
    left join myTable1 on male=myTable1.id
    order by myTable.id
    
    on the way result
    
    (1) Tom
    (2) Chris
    (3) Rudy
    (4) Jack
    The code below is one of trials but failed.
    Code:
    trial code2
    
    (select myTable.id,name from myTable
    left join myTable1 on male=myTable1.id)
    
    UNION
    
    (select myTable.id,name from myTable
    left join myTable1 on female=myTable1.id)
    
    order by myTable.id
    
    trial result
    
    mysql_fetch_array(): supplied argument is not a valid MySQL result resource.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you've done this before, joon

    you need one query, with two joins, using table aliases
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    life is like a box of chocolates, you never know which one you're going to get. over and over again.

  4. #4
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you've done this before, joon
    link, please.


    Quote Originally Posted by r937 View Post
    you need one query, with two joins,
    The code below seems not to work.
    Code:
    left join myTable1 on male=myTable1.id
    left join myTable1 on female=myTable1.id
    I guess I have to create one more cyber table (t1) in query ...

    Quote Originally Posted by r937 View Post
    using table aliases
    What does "aliases" here mean in English?



    Quote Originally Posted by guelphdad View Post
    life is like a box of chocolates, you never know which one you're going to get. over and over again.
    life is like a box of chocolates,
    you eat fast your life will critically down with fat, diabetes, or something.
    you eat steadily the box will be empty at last.

  5. #5
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,000
    Mentioned
    101 Post(s)
    Tagged
    0 Thread(s)
    Table aliases is the use of AS

    eg.

    Say you were dealing with fixtures for a football league (soccer), there's always a home team and an away team, the details for each are all kept in a table called "teams" so for the home team the table alias would be done by (in the FROM clause):
    Code SQL:
    teams AS home_team

    then in the same query (also in the FROM clause) for the away team the table alias would be done:

    Code SQL:
    teams AS away_team
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  6. #6
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,032
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    Off Topic:


    Quote Originally Posted by dotJoon View Post
    life is like a box of chocolates,
    you eat fast your life will critically down with fat, diabetes, or something.
    you eat steadily the box will be empty at last.
    Shame you missed that reference. Forrest Gump. Know thou classics
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dotJoon View Post
    link, please.
    i am not the keeper of your links

    ~you~ look it up

    hint: October, 2006

    example shows a join to the same table twice, using table aliases

    i am very tired of you refusing to remember this stuff

    please, search all your threads before asking the same question again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    please, search all your threads before asking the same question again
    ad infinitum, ad nauseum


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
  •