Problems while trying to make a query with variables in the conditions (stored procedure)

Question

Hi!! I'm having a problem. I'm trying to do a query... I remember that in the past I did something like this but today this query is returning nothing, no error, no data, just nothing... the query is something like this:

SELECT field1, @variableX:=field2
FROM table
WHERE 
    (SELECT COUNT(fieldA) FROM table2 WHERE fieldB=@variableX AND fieldC=0)>0 AND
    (SELECT COUNT(fieldA) FROM table2 WHERE fieldB=@variableX AND fieldC=4)=0;

I also tried this query but it didn't work (also it gaves no error):

SELECT field1, @variableX:=field2,
@variableY:=(SELECT COUNT(fieldA) FROM table2 WHERE fieldB=@variableX AND fieldC=0),
@variableZ:=(SELECT COUNT(fieldA) FROM table2 WHERE fieldB=@variableX AND fieldC=4)
FROM table
WHERE @variableY>0 AND @variableZ=0;

As you can see, what I'm trying to do in the 1st query is use a variable in the conditions. In the 2nd query I'm trying to create some variables and evaluate them in the conditions. At the end in the 2nd query the @variableY=1 AND @variableZ=0 but I don't know why the query returns an empty data set.

What could be wrong here??? Any comment or suggestion is welcome!!! Thanks!!! Bye!!!

Answer 1

IIRC, select fields are only executed after a where clause has been satisfied. So the @variableX will always be empty during the entire execution of the where clause... For the same query (IF I understand you correctly) you can do:

SELECT field1, field2
FROM table AS a
WHERE 
    (SELECT COUNT(fieldA) FROM table2 WHERE fieldB=a.field2 AND fieldC=0)>0 AND
    (SELECT COUNT(fieldA) FROM table2 WHERE fieldB=a.field2 AND fieldC=4)=0;

Answer 2

You don't need variables, subqueries, or COUNT to solve this problem.

SELECT DISTINCT t1.field1, t1.field2
FROM mytable t1
INNER JOIN mytable2 t2 ON t1.field2 = t2.fieldB AND t3.fieldC = 0
LEFT OUTER JOIN mytable2 t3 ON t1.field2 = t3.fieldB AND t3.fieldC = 4 
WHERE t3.fieldB IS NULL

You wanted nonzero matches for the t2 case, which is true if the inner join is satisfied.

And you wanted zero matches for the t3 case, which is true if the outer join is not satisfied (and therefore t3.* would be NULL).

---

@ircmaxell is correct that select-list expressions are evaluated only after the row passes the conditions in the WHERE clause. This is good, because if you have a costly expression in your select-list, but the WHERE clause is going to filter out 99% of rows, it would be wasteful to evaluate the costly select-list expression for all those rows, only to discard them.