CODEWITHSUNDEEP
mysqlif-statement
max_
max_

Reputation: 24481

If statement in MySQL

I would like to use an if statement in a MySQL query to SELECT all of the information in a database where... if it returns true, or return the COUNT of the column named aid.

Pseudocode: 

IF (COUNT(`AID`) > 0) {
  SELECT * FROM `a_votes` WHERE `aid` = '1'
} ELSE {
  RETURN 'No Rows'
}

Please could you tell me how I could do this?

Edit---

I tried the following query, but I am receiving the error Operand should contain 1 column(s)

SELECT IF( (

SELECT COUNT( aid ) AS  `count` 
FROM  `a_votes` 
WHERE  `aid` =1 ) =0,  'true', (

    SELECT * 
    FROM  `a_votes` 
    WHERE  `aid` =1
  )

) AS message

Upvotes: 0

Views: 406

Answers (2)

Bohemian
Bohemian

Reputation: 424983

SELECT * FROM (
    SELECT COUNT(*) as COUNT FROM a_votes WHERE aid = '1'
) x WHERE COUNT > 0;

If no rows match, a simple COUNT(*)) will returns a single row with 0 for the count. Wrapping the usual COUNT(*) query in an outer query that eliminates that condition will return no rows.

Edited:

Thanks to @bisiclop, this is a better solution:

SELECT COUNT(*) as COUNT
FROM a_votes
WHERE aid = '1'
HAVING COUNT(*) > 0;

Upvotes: 1

biziclop
biziclop

Reputation: 14596

If I wanted to transcribe your pseudocode to SQL, this would be the result, but I think it is unnecessarily complex, so it is just a demonstration:

/* this query might return zero rows, so beware */
SELECT
  (SELECT COUNT(`aid`) > 0 FROM `a_votes`) AS the_count,
  the_row.*
FROM
  (SELECT * FROM `a_votes` WHERE `aid` = '1') AS the_row

The same using LEFT JOIN, it must return at least one row:

SELECT *
/* count will create exactly 1 row */
FROM      (SELECT COUNT(`aid`) > 0 FROM `a_votes`)    AS the_count
/* this is allowed to return 0,1,...N rows */
LEFT JOIN (SELECT * FROM `a_votes` WHERE `aid` = '1') AS the_row

Upvotes: 0

Related Questions