[EMAIL PROTECTED] wrote:
Jacques Brignon <[EMAIL PROTECTED]> wrote on 11/09/2005 09:58:07 AM:
I have a query which scans a subscription database to locate 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
You are asking the database to return with data it does not have. Can you
not detect the fact that you found no records and use that in your
application code to supply a default date? That would be much easier to
implement and maintain than any database-based solution. The SQL can
become quite convoluted when you start trying to simulate missing values.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
I think this is two separate questions: "How do I get something rather than
nothing when there is no matching row?" and "How do I change the result to
something else if it satisfies certain conditions?"
I suspect getting mysql to return something rather than nothing may be just a
matter of using a LEFT JOIN. Trouble is, we don't have enough information to
tell. What goes in table transaction? What goes in table ligne_transaction?
Which table contains the columns id_ltrsn, date_fin_ltrsn, and cd_nature_ltrsn?
If Joe subscribes to "MySQL Today", what does the data look like? On the other
hand, if Joe doesn't subscribe to "MySQL Today", what does that look like?
I would expect there's a row for Joe somewhere (a subscriber table), a row for
"MySQL Today" somewhere (a periodical table), and a row somewhere (a
subscription table linking the first two) that indicates the subscription, if it
exists. Something like:
Subscriber
id
name
Periodical
id
name
Subscriptions
id
s_id
p_id
date_fin_ltrsn
With that arrangement, we can easily find when Joe's subscription to "MySQL
Today" ends:
SELECT MAX(date_fin_ltrsn)
FROM Subscriber s
JOIN Periodical p
LEFT JOIN Subscriptions t ON s.id = t.s_id AND p.id = t.p_id
WHERE s.name = 'Joe'
AND p.name = 'MySQL Today'
GROUP BY s.name, p.name;
If there are any matching subscriptions, you get the latest date_fin_ltrsn. If
there are no matching transactions, you get NULL.
Now, you also wanted to change the output based on two conditions. If
MAX(date_fin_ltrsn) is prior to today, or if there is no subscription, you want
to get today's date. We can fix the first case with the GREATEST() function,
and the second case with COALESCE():
SELECT COALESCE(GREATEST(MAX(date_fin_ltrsn), CURDATE()), CURDATE())
...
the rest remains the same.
Can you translate that to your tables? If not, perhaps if you described your
tables, someone could write a query to fit.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
