Need Some Help in Query

Question

Post reply

Need Some Help in Query

raghu-935218

SSC Eights!

Points: 903
More actions
April 15, 2009 at 10:10 am

#130579

Hi
I want to find the Earliest date and RID from the table.I am joining three tables here....But i am always getting the max date
SELECT R.RID,MIN(CONVERT(DATETIME, F.from_dos, 12)) AS EARLYRDATE
FROM R7541f6 F,DISABILITYCALIMS R ,TABA1 T
WHERE R.RID=F.RID AND
F.DIAG=T.ICDCOD and
T.[Per Se Disabling]='YES'
GROUP BY R.RID
But when i use the below query by joining only 2 tables i am getting the min date
SELECT R.RID,MIN(CONVERT(DATETIME, F.from_dos, 12)) AS EARLYRDATE
FROM R7541f6 F,DISABILITYCALIMS R
WHERE R.RID=F.RID
GROUP BY R.RID
I am unable to figure out the reason.....please help me
Thanks

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

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

rgillings SSCrazy Points: 2114 More actions · Answer 1

Looks to me like your additional filters:

F.DIAG=T.ICDCOD and

T.[Per Se Disabling]='YES'

are having an impact on the results.

Nabha SSCrazy Eights Points: 8534 More actions · Answer 2

Yeah, it could be the reason that your RID entry with minimum date is getting filtered out because of that criteria and third table join.

P.S:Moreover if not in this case, you might have some problems with this style of join (old style SQL-89). You should start thinking about writing the query in SQL 92 format.Thanks.

---------------------------------------------------------------------------------

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

Pakki (4/16/2009)
You should start thinking about writing the query in SQL 92 format.Thanks.

Using this excellent suggestion, try the following:

SELECT R.RID, F.from_dos, T.ICDCOD

--MIN(CONVERT(DATETIME, F.from_dos, 12)) AS EARLYRDATE

FROM R7541f6 F

INNER JOIN DISABILITYCALIMS R ON R.RID = F.RID

LEFT JOIN TABA1 T ON F.DIAG = T.ICDCOD AND T.[Per Se Disabling] = 'YES'

--GROUP BY R.RID

The join to the third table is now a LEFT JOIN (having moved all of the criteria from the WHERE into the join clause), and the GROUP BY is commented out. Have a look at the result. Rows which would be eliminated by an INNER JOIN (same as your original query) will have NULL value for column T.ICDCOD.

This doesn't mean that a LEFT JOIN is appropriate for your business case.

^{“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