Thanks to all those who provided feed back. As a result I found two ways of solving my problem, one is going along the LEFT JOIN track, and the other one is to run a script before doing the query itself whioch is doable in my environment without modifying the core software and which can then do easily more logic and put the result in a temp table which I then use for the query.
After trying the first I decided for the second one which can more easily cope with all the peculiarities found in the data. Jacques Brignon Selon Michael Stassen <[EMAIL PROTECTED]>: > [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] > > -- Jacques Brignon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
