473,881 Members | 1,641 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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,hier archy 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.proje ct='CLUSTERII' AND
hierarchy.missi on='CLUSTER-4' AND
hierarchy.exper iment='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 2107
jo**@swri.edu wrote:
SELECT COUNT(*) AS COUNT FROM hd_entries,hier archy 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
2143
by: EJC | last post by:
Hi Folks, I've been trying to build a query from a form of dialogue boxes I have created that holds search criteria for my main database. I have been able to get the query to retrieve the data selected in the relevant dialogue boxes and perform the query on these boxes. However my problem lies in the fact that if no choice is selected in the dialogue box I want the query to default (or get passed) a wildcard that will mean it will...
4
2669
by: DOTNET | last post by:
Hi, Anybody help me regarding this error: I am assigning the values to the session variables when the button is clicked and passing these session variables to the next page and when I am printing these session variables they are printing. After that I am assigning these things in hidden object and in the form submit action I am receiving these hidden values like the following:
6
5010
by: James Radke | last post by:
Hello, I have a multithreaded windows NT service application (vb.net 2003) that I am working on (my first one), which reads a message queue and creates multiple threads to perform the processing for long running reports. When the processing is complete it uses crystal reports to load a template file, populate it, and then export it to a PDF. It works fine so far....
28
1911
by: Siv | last post by:
Hi, If I run the following: strSQL = "Select * FROM Clients;" da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter dt = New Data.DataTable da.Fill(dt) 'pour in the data using the adapter
9
2199
by: KenLee | last post by:
I made an application which includes classic asp page and asp.net page. when I tried to redirect from classic asp page to asp.net 2.0 page, it works under my local IIS directory. ex) <a href="default.aspx?store=<%=rs("store")%>"><%=rs("store")%></a> However, When I copy that application into one of IIS directory in another intranet server, it doesn't work.
7
7364
by: rguarnieri | last post by:
Hi! I'm trying to create a query with a boolean expression like this: select (4 and 1) as Value from Table1 this query return always -1, but when I make the same calculation in visual basic, the value returned is 0. Can anyone tell me why the expression (4 and 1) return different value
4
4293
by: Alan Silver | last post by:
Hello, I have an error logging system on one of my sites, so that if an unexpected exception occurs, I get an e-mail sent. The e-mail includes the name of the page on which the exception occurred, so I can track it down. I have been getting a lot of e-mails recently showing the source of the exception as being WebResource.axd, and I'm not sure what's happening. I wonder if anyone here has any ideas.
9
3952
by: pic078 via AccessMonster.com | last post by:
I need serious help - I have a frontend/backend Access database (2 MDE Files) that remains stuck in task manager after exiting the application - you can't reopen database after exiting as a result - I have read every post out there and spent hours trying to figure out the problem with no success whatsoever - I have constrained the problem to one form however, and I think it's hiding somewhere in my code associated with this form, which is...
0
1277
by: smtwtfs007 | last post by:
Any body has any idea about his error please? I got this error at the time of creating crystal report. I tried everything to resolve this error. But I could not. Please help. Exception has been thrown by the target of an invocation. at System.Reflection.RuntimeMethodInfo.InternalInvoke(Object obj, BindingFlags invokeAttr, Binder binder, Object parameters, CultureInfo culture, Boolean isBinderDefault, Assembly caller, Boolean...
3
3025
by: jambonjamasb | last post by:
Hi I have two tables: email_tbl Data_table Data table is is used to create a Form Data_form
0
9776
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11100
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10718
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10402
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9555
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7953
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7111
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
2
4196
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3225
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.