Post reply

1 table 2 times in a query

  • August 28, 2008 at 10:08 am

    #191936

    I have a Cognos query, simplified as following:

    Select

    T1.somecolumn,

    T2.somecolumn,

    T3.somecolumn,

    T11.anothercolumn

    From

    Table1 T1, Table2 T2, Table T3, Table1 T11

    Where

    T1.id = T2.id and

    T2.id = T3.id and

    T11.id = T3.id

    I am trying to understand what’s the meaning or benefit to have Table1 in the FROM twice with different alias. The reason I ask is because this Table1 is a big table, logical reads is very high, and from execution plan, it counted Table1 as 2 different tables. Please help. Thanks.

  • August 28, 2008 at 10:18 am

    #864547

    It's a self join. You may need information from two rows.

    A simple example is an employee table that has a manager column. The manager column references the ID of another employee, so you have a self reference.

  • August 28, 2008 at 10:22 am

    #864550

    an example might be finding students with the same name but different user

    select *

    from tblStudent s1

    inner join tblStudent s2 on s1.firstname = s2.firstname

    and s1.lastname = s2.lastname

    and s1.userid <> s2.userid

  • August 28, 2008 at 10:38 am

    #864567

    thanks for your very quick replies.

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

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