Query with a derived table

[mwolf00]

I think I must be suffering from SQL atrophe... This is not a difficult query, but my brain is a little fuzzy this morning.


tCBR
cbr - primary key
region


tScore
cbr - foriegn key
enteredTime
score

What I need is to get all of the CBRs from a given region and their most recent score(or "0" if no score is recorded). Any given CBR may or may not have one or more scores recorded.


Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[DLSQL]

--A Join with a correlated Subquery should do the trick
-- Assumming that enteredTime is a DateTime field.

SELECT r.cbr, Region, enteredTime, Score
FROM tCBR r
JOIN tScore s
ON r.cbr = s.cbr
WHERE enteredTime = (SELECT Max(enteredTime) FROM tScore
WHERE tScore.cbr = r.cbr)

DL
MCDBA, MCSD, MCT, etc.

 

[mwolf00]

Thanks for the reply.

I still need to return all of the CBRs from tCBR where no score is recorded in the tScore table. Your query works fine when multiple scores exist but I get no CBR unless there is a score associated with it.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[mwolf00]

Hmmm - I think the fog may be clearing....

select c.i_agmt, s2.pct
from dat_cbr c
left JOIN (select s1.cbr, max(s1.entered) entered from da_scores s1 group by s1.cbr) s on s.cbr= c.i_agmt
left JOIN da_scores s2 on s2.cbr = s.cbr and s2.entered = s.entered
where c_region = '10'

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook