469,331 Members | 4,130 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,331 developers. It's quick & easy.

Help on boolean SQL query


I have a SQL query where I get the count of something, but what I really
want is a yes/no (boolean) answer. Here is my query:

SELECT COUNT(*) AS COUNT FROM hd_entries,hierarchy WHERE
IFNULL (BETWEEN_TIMES (b_yr, b_day, b_msec, e_yr, e_day, e_msec,
2001, 31, 0, 2001, 31, 63720000) >0, 1) AND
hd_entries.data_key=hierarchy.data_key AND
hierarchy.project='CLUSTERII' AND
hierarchy.mission='CLUSTER-4' AND
hierarchy.experiment='PEACE';

Is there a way to change this such that it will only return a 0 or 1. Our
hope is that this makes the series of queries that I need to do go that much
quicker. The BETWEEN_TIMES function is a routine that I wrote that returns
a -1, 0, 1 if the entry sent in to it is between the given times.

I'm using MySQL v3.23.58 if it matters.

Thanks!
Joey

Jul 23 '05 #1
1 1887
jo**@swri.edu wrote:
SELECT COUNT(*) AS COUNT FROM hd_entries,hierarchy WHERE ...

Is there a way to change this such that it will only return a 0 or 1. Our
hope is that this makes the series of queries that I need to do go that much
quicker.


You could try fetching just one row using LIMIT. Then the query would
return an empty result set indicating a count of 0, or else a result set
of 1 row indicating a count greater than 0. This should run pretty
quick, since MySQL short-circuits when using LIMIT. Once it finds the
number of rows to satisfy the limit, it stops working on the query.

SELECT 1 AS I_FOUND_ONE
FROM hd_entries, hierarchy
WHERE ...
LIMIT 1

If you need exactly one row with a 1 or a 0, you could do the same query
as you had, but use IF() to return 1 or 0.

SELECT IF(COUNT(*) > 0, 1, 0) AS I_FOUND_ONE
FROM hd_entries, hierarchy
WHERE ...

Other solutions I could suggest involve using subqueries within the
EXISTS predicate, but you said you're using MySQL 3.23, so those
solutions won't work for you.

Regards,
Bill K.
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by EJC | last post: by
28 posts views Thread by Siv | last post: by
9 posts views Thread by pic078 via AccessMonster.com | last post: by
reply views Thread by smtwtfs007 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.