Solved

Boolean alternative to SELECT * FROM sometable WHERE condition

Posted on 2004-08-11
9
1,066 Views
Last Modified: 2006-11-17
is there a Boolean alternative to SELECT * FROM sometable WHERE condition ? I only need to know if a row or several exists with that certain condition or not, no need to fetch the data. I want to do this to optimize my mysql queries. Is there such a way ?
0
Comment
Question by:iconpacific
9 Comments
 
LVL 11

Accepted Solution

by:
Sven earned 500 total points
ID: 11770925
SELECT RowCount = COUNT(*) FROM sometable WHERE condition

When RowCount > 0 then you have data inside!
0
 
LVL 5

Expert Comment

by:hkamal
ID: 11771716
select CASE COUNT(*) when 0 then "False" else "True" end from TableName WHERE ColumnName=Condition

OR if you prefer 1,0 then

select CASE COUNT(*) when 0 then 0 else 1 end from TableName WHERE ColumnName=Condition
0
 
LVL 11

Expert Comment

by:Sven
ID: 11771742
@hkamal: Would be no difference to mine! For example in VB:

If rs("rowcount") = 0 Then
Else
End If

Or yours:

If rs(0) = 0 Then
Else
End If

You see? No difference at all!
0
 
LVL 14

Expert Comment

by:psadac
ID: 11773765
to DarthSonic :
do you really use mysql ? yes there is a big difference between your query and hkamal one : yours does not work.

RowCount = COUNT(*)     is incorrect

COUNT(*) AS RowCount   will work

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 33

Expert Comment

by:snoyes_jw
ID: 11773829
You can also use SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS(). If you put a LIMIT on the first query, you don't have to return the entire result set to the client.  I think the COUNT(*) option is probably easier in this case, though.

http://dev.mysql.com/doc/mysql/en/Information_functions.html
0
 
LVL 26

Expert Comment

by:ushastry
ID: 11798881
try this..

SELECT ifnull( count( * ) , NULL ) AS Total
FROM tableName
WHERE columnName = 'columnCondition'
0
 
LVL 11

Expert Comment

by:Sven
ID: 11807290
@ushastry
isn't it:

SELECT isnull( count( * ) , 0) AS Total
FROM tableName
WHERE columnName = 'columnCondition'

First: isNull instead of ifNull (in MS SQL it is)
Second set to zero when NULL, otherwise it would be the same as before calling the function: null ;)

At all you do not need isNull, cause COUNT(*) will NEVER return NULL!
0
 
LVL 26

Expert Comment

by:ushastry
ID: 11807328
Hi DarthSonic,

Im not that much familiar with MS SQL.. but in mysql I think it is IFNULL.

0
 
LVL 11

Expert Comment

by:Sven
ID: 11807348
never the less it is unnecessary to call this function as COUNT(*) will ever return zero if nothing is found ;)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now