CODEWITHSUNDEEP
mysqlsql
mega-crazy
mega-crazy

Reputation: 858

Select statement within if condition not working with additional parameters

I'm trying to do this

SELECT 
  table1.*,
  table2.id as t2id 
FROM 
  table1 as t1 
INNER JOIN table2 as t2 
  ON t2.field1 = t1.field2 
  AND t1.field2 = 'value'
  AND IF(SELECT COUNT(*) FROM table2 WHERE id = 10 > 0)

It error says

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 

'SELECT COUNT(*) FROM table2 WHERE id = 10 > 0) LIMIT ' at line 1

I know the error is with the if condition as when I remove it, it works, but my thinking is the select will return a null value if it was not successful i.e. it didn't find anything in the table with id 10 from table2.

Upvotes: 1

Views: 101

Answers (3)

Slowcoder
Slowcoder

Reputation: 2120

Try removing the IF.

SELECT 
  table1.*,
  table2.id as t2id 
FROM 
  table1 as t1 
INNER JOIN table2 as t2 
  ON t2.field1 = t1.field2 
  AND t1.field2 = 'value'
  AND (SELECT COUNT(*) FROM table2 WHERE id = 10) > 0;

Upvotes: 1

Stephan
Stephan

Reputation: 8090

Try this (make sure that you exec the queries on the same session):

SELECT COUNT(*) INTO @COUNTER FROM table2 WHERE id = 10 ;

SELECT 
  table1.*,
  table2.id as t2id 
FROM 
  table1 as t1 
INNER JOIN table2 as t2 
  ON t2.field1 = t1.field2 
  AND t1.field2 = 'value'
  AND @COUNTER > 0 ;

Upvotes: 0

Tobias
Tobias

Reputation: 1682

You're missing the "then" part of your if.

It has too look like IF (condition, then, else), but you're just doing the condition without any output.

Try it that way:

AND IF((SELECT COUNT(*) FROM table2 WHERE id = 10) > 0, 'true', 'false')

Upvotes: 1

Related Questions