Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2006
    Posts
    41

    Unanswered: how to join a table with itself

    Hi,

    I have a table in my database with the structure:


    +---+-------------+----------
    | id | surname | reviewer|
    +---+-------------+----------
    | 1 | smith | 2 |
    ------------------------------
    | 2 | jones | 4 |

    What I want is to output the surname of both the reviewer and the reviewee so I would have:

    +---+-------------+----------
    | id | reviewee | reviewer|
    +---+-------------+----------
    | 1 | smith | jones |
    ------------------------------
    | 2 | jones | Riley |




    With the reviewer 'referencing' id and them both being in the same table I can't figure out how to do it.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Code:
    select t1.id, t1.surname, t2.surname
    from MyTable t1,
           MyTable t2
    where t2.id = t1.reviewer
    You didn't give the name of the table so I called it MyTable.
    It''s a good idea to have both firstname and surname in the table.
    It would be better if you called the reviewer field reviewer_id.

    Mike

  3. #3
    Join Date
    Mar 2006
    Posts
    41
    Quote Originally Posted by mike_bike_kite
    Code:
    select t1.id, t1.surname, t2.surname
    from MyTable t1,
           MyTable t2
    where t2.id = t1.reviewer
    You didn't give the name of the table so I called it MyTable.
    It''s a good idea to have both firstname and surname in the table.
    It would be better if you called the reviewer field reviewer_id.

    Mike
    That's excellent.

    Cheers

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can we use JOIN syntax please?
    Code:
    SELECT t1.id
         , t1.surname
         , t2.surname
    FROM   MyTable t1
     INNER
      JOIN MyTable t2
        ON t2.id = t1.reviewer
    Also, a LEFT JOIN will allow you to show those without reviewers.
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Can we use JOIN syntax please?
    You're welcome to use whatever syntax you feel like George. I shall do similar

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    It would be better if you called the reviewer field reviewer_id.
    you're welcome to use whatever column names you feel like, mike

    the rest of the world shall do similar
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Once uraknai said thanks I thought we'd finished but ...

    The new join syntax works great and has it's advantages but I just happen to find the older syntax simpler to write and understand - for me and for others. Thanks for the info on left joins but you might be surprised to learn that null joins are easily possible with the old syntax. Luckily they aren't required by uraknai's original query.

    The column names were supplied by uraknai and so I left them out so he could have a little something to do.

    I only commented on the field names in the table cause it appears to me to be better practice to call the reviewer field, which is an id, reviewer_id. I assume that the latest fad book that you guys are reading/writing has some special naming convention ti_reviewer_fk_MyTab perhaps. What naming convention would you suggest?

    Mike

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    naming conventions are just that...conventions
    it doesn't really matter whether you use columnname_id or columnameid or ColumnNamID.

    I uised to use _ as a separator for things like ID, these days I tend to find ColumnNameID is the more common convention, so I tend to use that.

    providing its consistent and applicable to all code developed in that shop. there's nowt worse in my books in finding multiple conventions in one site, and undoubtedly its easier if everyone used the same convention

    AFIAK the old style syntax is deprecated and join is the preferred syntax.
    personally I find it easier with newbies to get them to think in terms of blocks within SQL .. the JOIN being the 'proper' place to define relationships, the WHERE the filter, and so on. but Im not prescriptive...

    AS the JOIN syntax is the current preferred syntax Id rather start people using JOIN rather than WHERE..... I think its fine for people who are familiar with WHERE or for legacy code, it may not be a smart choice for current or proposed applications. Whether the WHERE join syntax will ever be phased out is a moot point, but in my books its no reason to start people off using it.

    The JOIN syntax makes more logical sense to me, it also is more capable, and perhaps more importantly more transparent than burying the joins in a WHERE clause.. that transparency has more to do with the legibility rather than the familiarity of "that's the way I've always done it".

    Im currently paying some of the pain of moving into a .NET environment, and hating it, because I can't use the old styles that I'm familiar with. Id like to continue to using the familiarity of VB but instead Ive got to relearn .NET.. its a pain but it has to be done

Posting Permissions

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