Post reply

It is possible?

  • October 12, 2005 at 5:02 pm

    #88181

    It is possible to get this resultset?

    Name Manager
    Paolo NULL
    Pedro Paolo

    Helen Paolo

    Victoria Pedro

     
    Data:
    ID     Name Mag_ID
    1111 Paolo NULL

    2222 Pedro 1111

    3333 Victoria 2222

    4444 Helen 1111

     
    my query:
    select E1.name, E.name as Manager from dbo.employee E, dbo.employee E1

    where E.id = E1.mag_id

     
    my resultset:
    Name Manager
    Pedro Paolo

    Helen Paolo

    Victoria Pedro

     

    I miss the first row...

     
     

  • October 12, 2005 at 5:11 pm

    #596961

    select E1.name, E.name as Manager
    from dbo.employee E1

       LEFT JOIN dbo.employee E ON E.id = E1.mag_id

    _____________
    Code for TallyGenerator

  • October 12, 2005 at 5:14 pm

    #596962

    The query will work but reading this article may give you a little more in depth knowledge about self referencing tables.

    http://www.developersdex.com/gurus/articles/112.asp

  • October 12, 2005 at 5:20 pm

    #596965

    Thanks Sergiy! I got it...It should be RIGHT JOIN...

  • October 13, 2005 at 4:48 am

    #597040

    Left of Right as the same - they are both OUTER joins.

    Left or Right depends on which order you mention your tables.

    FROM tableA LEFT JOIN tableB

    is the same as

    FROM tableB RIGHT JOIN tableA

    but..

    FROM tableA LEFT JOIN tableB

    is NOT the same as

    FROM tableA RIGHT JOIN tableB

     

    Always be careful about which order tables are mentioned in outer joins.

    /Kenneth

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply