I think the answer you're looking for is to use an in-lined table query (I
think the technical term is correlated sub-query), like so:
SELECT prospectId,
actions.dateAction
FROM prospects
LEFT JOIN (SELECT prospect, MAX(dateAction) as dateAction
FROM actions
WHERE dateAction <= Now()) as actions ON
(prospects.prospectId=actions.prospect)
You can substitute now for whatever 'get the date for today' function your
server supports.
>Hi,
>
>I have this query:
>SELECT
> prospectId,
> actions.dateAction
> FROM (prospects
> LEFT JOIN actions ON prospects.prospectId=actions.prospect)
>
>I would like to add a column that would be the date of the "most
>imminent" action,
>ie. the one with the sooner -- but not passed -- date in dateAction, if
>there is at least one action, null otherwise.
>
>Any one has an idea?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3146
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
