Solved

Boolean alternative to SELECT * FROM sometable WHERE condition

Posted on 2004-08-11
9
1,078 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 
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:Umesh
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:Umesh
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

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

623 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