Help with Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Join

Author

Topic

shailesh18
Starting Member

8 Posts

Posted - 2011-10-03 : 04:20:32

Dear friends
Kindly help me with this query

I have 2 table P_S_T and AND P_S_CertificationStatus_T

P_S_T


---------------
PID     SID     
---------------
1      1       
1      2       
1      3       
2      1       
2      5       
---------------

P_S_CertificationStatus_T


------------------------------------
PID  SID   CID     Cdt_DT (dd-MM-yy)
-------------------------------------
1     1    2       01-08-11
1     1    1       01-01-11
1     2    2       05-03-11
2     5    5       18-06-11
2     5    5       18-03-11
--------------------------------------

The Output, i want is


------------------------------------
PID  SID   CID     Cdt_DT (dd-MM-yy)
-------------------------------------
1     1     2       01-08-11
1     2     2       05-03-11
1     3     NULL    NULL
2     1     NULL    NULL
2     5     5       18-06-11
--------------------------------------

Red color indicates row is not in table P_S_CertificationStatus_T
Again, from table 2 i.e P_S_CertificationStatus_T, its only printing CID and Cdt_DT which is latest sorted date wise descending.

Kindly let me know, how to achieve this.
With reference to the above cited issue, i know, how to sort the records and take the latest from P_S_CertificationStatus_T, i am somehow failing in JOINS.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=166162

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 04:47:24

[code]SELECT pst.PID,
pst.SID,
psc.CID,
psc.Cdt_DT
FROM P_S_T pst
LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY PID,SID ORDER BY Cdt_DT DESC) AS Rn,*
FROM P_S_CertificationStatus_T)psc
ON psc.SID= pst.SID
AND psc.PID = pst.PID
AND psc.Rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shailesh18
Starting Member

8 Posts

Posted - 2011-10-03 : 05:36:02

Dear Visakh

Genius.
Would you please explain me, how it works.
Thanks for wonderful solution.
Thank you very much.

quote:
Originally posted by visakh16

SELECT pst.PID,
pst.SID,
psc.CID,
psc.Cdt_DT
FROM P_S_T pst
LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY PID,SID ORDER BY Cdt_DT DESC) AS Rn,*
FROM P_S_CertificationStatus_T)psc
ON psc.SID= pst.SID
AND psc.PID = pst.PID
AND psc.Rn=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 06:07:39

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources