#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    Germany
    Posts
    230
    Rep Power
    16

    Select from 3 rows, 2 tables into one result


    Hi all,

    sorry for the crappy title, but I couldn't find a better one.

    Here's my problem.

    table 1 - article table
    Code:
    id, title, description
    1, 923, 813
    table 2 - translation table
    Code:
    id, value
    923, article title
    813, article description lorem ipsum...
    I want to select everything from my article database, but with its' title and description translated.
    I cannot use joins can I? Afaik I cannot join the same table more than once.

    Unfortunately I don't even have a clue how to solve it.

    Thanks in advance for any hint or solution!

    kind regards

    Sven
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    103
    Rep Power
    65
    Afaik I cannot join the same table more than once.
    You would join the same table twice, using a different alias name in each join, such as t1, t2, ... Then use the correct alias name in the references to the columns.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Code:
    SELECT articles.id
         , trans1.value AS title
         , trans2.value AS description
      FROM articles
    INNER
      JOIN translations AS trans1  
        ON trans1.id = article.title
    INNER
      JOIN translations AS trans2
        ON trans2.id = article.description
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo