how to convert the below subquery to joins

Question

Post reply

how to convert the below subquery to joins

surendiran.balasubramanian

SSC Rookie

Points: 49
More actions
April 19, 2013 at 2:41 am

#394631

how to convert the below subquery into join ..
SELECT CtryId, WrkflwId, Dt
FROM stepHistory H
WHERE EXISTS
(SELECT * FROM
(SELECT WrkflwId, CtryId , max(DtTm) DtTm
FROM stepHistory
GROUP BY WrkflwId, ctryId
) S
WHERE S.WrkflwId = H.WrkflwId AND S.DtTm = H.DtTm AND H.CtryId = S.CtryId)
AND SteTyId = 2
----I have use the above query as inner join because i have to run the above query into an datawarehous applicance which wont support subquery
please help
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

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

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

Something like this

SELECTH.CtryId, H.WrkflwId, H.Dt

FROMstepHistory H

INNER JOIN(

SELECTWrkflwId, ctryId, MAX( DtTm ) AS DtTm

FROMstepHistory

GROUP BY WrkflwId, ctryId

) AS SH ON H.WrkflwId = SH.WrkflwId AND H.CtryId = SH.CtryId AND H.DtTm = SH.DtTm

WHEREH.SteTyId = 2

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

surendiran.balasubramanian (4/19/2013)
how to convert the below subquery into join ..
SELECT CtryId, WrkflwId, Dt
FROM stepHistory H
WHERE EXISTS
(SELECT * FROM
(SELECT WrkflwId, CtryId , max(DtTm) DtTm
FROM stepHistory
GROUP BY WrkflwId, ctryId
) S
WHERE S.WrkflwId = H.WrkflwId AND S.DtTm = H.DtTm AND H.CtryId = S.CtryId)
AND SteTyId = 2
----I have use the above query as inner join because i have to run the above query into an datawarehous applicance which wont support subquery
please help
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

If the row in the inner query having the maximum date per CtryId and WrkflwId has a different SteTyId to the outer query, the outer query will drop the row unless the row with SteTyId = 2 has the same value for DtTm. Are you absolutely sure this query does what you are expecting it to?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden