February 13, 2014 at 2:55 am
Hi There,
Please give some examples for more understanding on self join
thanks
February 13, 2014 at 3:01 am
Just few examples:
-- selecting orw with maximum date
SELECT *
FROM SomeTable st1
JOIN (SELECT SomeID, MAX(SomeDateColumn) mxSomeDateColumn
February 13, 2014 at 3:05 am
Just few examples:
-- selecting row with maximum date:
SELECT *
FROM SomeTable st1
JOIN (SELECT SomeID, MAX(SomeDateColumn) mxSomeDateColumn
FROM SomeTable GROUP BY SomeID) st2
ON st2.SomeID = st1.SomeID
AND st2.mxSomeDateColumn = st1.SomeDateColumn
-- selecting child rows from simple hierarchy table:
SELECT st1.*
FROM SomeTable st1
JOIN SomeTable st2
ON st2.SomeParentRecID = st1.RecID
-- joining to itself on non-id column to check if another record with the same details
SELECT st1.*
FROM SomeTable st1
JOIN SomeTable st2
ON st2.SomeDetailColumn = st1.SomeDetailColumn
WHERE st2.RecId != st1.RecId
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply