Possible to avoid a SELF JOIN without a Subquery?

• OJ Ferns

  Newbie

  Points: 0

  More actions

  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

• Steve Jones - SSC Editor

  Newbie

  Points: 0

  More actions

  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.

• OJ Ferns

  Newbie

  Points: 0

  More actions

  March 20, 2010 at 5:27 am

  #1137164

  As suspected, thanks Steve....