MySQL returns all rows when field=0 from SECOND Select query

Question

This case is similar to: S.O Question; mySQL returns all rows when field=0, and the Accepted answer was a very simple trick, to souround the ZERO with single quotes

FROM:

SELECT * FROM table WHERE email=0

TO:

SELECT * FROM table WHERE email='0'

However, my case is slightly different in that my Query is something like:

   SELECT * FROM table WHERE email=( 
         SELECT my_column_value FROM myTable WHERE my_column_value=0 AND user_id =15 LIMIT 1 )

Which in a sense, becomes like simply saying: SELECT * FROM table WHERE email=0, but now with a Second Query.

PLEASE NOTE: It is a MUST that I use the SECOND QUERY.

When I tried: SELECT * FROM table WHERE email='( SELECT my_column_value FROM myTable WHERE my_column_value=0 LIMIT 1 )' (Notice the Single Quotes on the second query)

MySql SCREAMED Errors near '(.

How can this be achieved

Any Suggestion is highly honored

EDIT1: For a visual perspective of the Query

See the STEN_TB here: http://snag.gy/Rq8dq.jpg

Now, the main aim is to get the sten_h where rawscore_h = 0;

The CURRENT QUERY as a whole.

SELECT sten_h
                FROM sten_tb
                WHERE rawscore_h =  (
                SELECT `for_print_stens_rowscore`
                FROM `for_print_stens_tb`
                WHERE `for_print_stens_student_id` =3
                AND `for_print_stens_factor_name` = 'Factor H' )

The result of the Second Query can be any number including ZERO.

Any number from >=1 Works and returns a single corresponding value from sten_h. Only =0 does not Work, it returns all rows

That's the issue.

CORRECT ANSWER OR SOLUTION FOR THIS

Just in case someone ends up in this paradox, the Accepted answer has it all.

SEE STEN_TB: http://snag.gy/Rq8dq.jpg

SEE The desired Query result here: http://snag.gy/wa4yA.jpg

You need the quotes only around the value: WHERE my_column_value='0' — Darius X., Nov 24 '14 at 18:10
@DariusX. That's what I said in the Question... Now the problem, **how to deal with the zero when it is the result of a Second Query??** — Universal Grasp, Nov 24 '14 at 18:12
@UniversalGrasp, try select cast(my_column as varchar(1)) from mytable — radar, Nov 24 '14 at 18:13
I get ERROR: **#1064** -*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 'varchar(1)) FROM `for_print_stens_tb` WHERE `for_print_stens_student_id`' at line 4`* — Universal Grasp, Nov 24 '14 at 18:14
@philipxy Please See EDIT1, It has an image of the actual table. Thank you again — Universal Grasp, Nov 24 '14 at 18:39
That is not a table definition. If your table is generated by code in another language give that code. Also avoid links when you can put things inline. Although that that enormous illustration is not useful here. Also it is clipped. — philipxy, Nov 24 '14 at 19:11

score 2 · Accepted Answer · answered Nov 24 '14 at 18:27

I believe your issue is with implicit datatype conversions. You can make those datatype conversions explicit, to gain control.

(The "trick" with wrapping a literal 0 in single quotes, that makes the literal a string literal, rather than a numeric.)

In the more general case, you can use a CAST or CONVERT function to explicitly specify a datatype conversion. You can use an expression in place of a column name, wherever you need to...

For example, to get the value returned by my_column_value to match the datatype of the email column, assuming email is character type, something like:

... email = (SELECT CONVERT(my_column_value,CHAR(255)) FROM myTable WHERE ...

or, to get the a literal integer value to be a string value:

 ... FROM myTable WHERE my_column_value = CONVERT(0,CHAR(30)) ...

SUPERB!!.... It worked when I tried the `(CONVERT(...,...))` trick. Thanking you again. (Accepted) — Universal Grasp, Nov 24 '14 at 18:42

philipxy · Answer 2 · 2014-11-26T04:25:06.777

If email and my_column_value are just indicating true or false then they should almost certainly be both BIT NOT NULL or other two-value type that your schema uses for booleans. (Your ORM may use a particular one.) Casting is frequently a hack made necessary by a poor design.

If it should be a particular user then you shouldn't use LIMIT because tables are unordered and that doesn't return a particular user. Explain in your question what your query is supposed to return including exactly what you mean by "15th".

(Having all those similar columns is bad design: rawscore_a, sten_a, rawscore_b, sten_b,... . Use a table with two columns: rawscore, sten.)

MySQL returns all rows when field=0 from SECOND Select query

CORRECT ANSWER OR SOLUTION FOR THIS

2 Answers2