Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Self Join - Examples Expand / Collapse
Author
Message
Posted Thursday, February 13, 2014 2:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 123, Visits: 329
Hi There,

Please give some examples for more understanding on self join

thanks
Post #1541079
Posted Thursday, February 13, 2014 3:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
Just few examples:

-- selecting orw with maximum date
SELECT *
FROM SomeTable st1
JOIN (SELECT SomeID, MAX(SomeDateColumn) mxSomeDateColumn



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1541081
Posted Thursday, February 13, 2014 3:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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






_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1541082
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse