I have a query which scans a subscription databse to locte the most recent
expiration date of the subscription to a given periodical or service to compute
the start date of a renewal.

It works fine when for a given person such a subscription exists. If none
exists, as expected the query produces no results.

Any suggestion on how to transform this query to produce a default value set
when no past subscription exists?

Here is the query:

SELECT
id_ltrsn, MAX(GREATEST(date_fin_ltrsn  + INTERVAL 1 MONTH, CURDATE())) AS
subs_start
FROM
ligne_trans, transaction
WHERE
transaction.id_trsn = ligne_trans.id_trans_ltrsn AND cd_nature_ltrsn = 2
AND ligne_trans.id_cntxt_ltrsn = 1
AND transaction.id_pers_trsn = 278
GROUP BY
ligne_trans.id_cntxt_ltrsn

When past subscipiton exixts it will produce as an example:

id_ltrsn | subs_start
---------------------
79       | 2006-11-25

When no past subscription exists I would like to get today's date as a result
instead of nothing, example:

id_ltrsn | subs_start
---------------------
0        | 2005-11-09


Thanks for any help you can provide

--
Jacques Brignon

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to