Post reply

Possible to avoid a SELF JOIN without a Subquery?

  • March 16, 2010 at 10:05 am

    #98641

    Hi,

    I'm wondering if there is a way to write a query such as this without using a SELF JOIN..

    SELECT TypeRelationship.pkid, TypeRelationship.ParentTypeId, TypeRelationship.ChildTypeId

    FROM ContactType AS ChildType

    INNER JOIN TypeRelationship ON ChildType.pkid = TypeRelationship.ChildTypeId

    INNER JOIN ContactType AS ParentType ON TypeRelationship.ParentTypeId = ParentType.pkid

    I guess what I'm asking is "Is there a way to write a query like this using only one instance of the ContactType table, and not using subqueries"....

    It is for a view, and I want to index that view, if this helps...

    Thanks,

    Oli

  • March 19, 2010 at 8:31 pm

    #1137109

    If these are related in a hierarchial manner, no. You need two instances in a self join to match up the values. If you have these columns indexed, this should not be a performance issue.

  • March 20, 2010 at 5:27 am

    #1137164

    As suspected, thanks Steve....

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

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