Page 1 of 1
Binary Select In Join Statement
Posted: Mon Feb 12, 2007 2:37 pm
by WanamakerStudios
Anyone know how to do a BINARY select in a JOIN statement?
Posted: Mon Feb 12, 2007 3:49 pm
by Weirdan
I must be thick and living under the rock, but I don't know what is 'binary select'. Please clarify.
Besides, posting the name of your db server software (such as DB2, Informix, MSSQL, MySQL or what-you-have-there) along with its version number would make it much easier to us to help you.
Posted: Tue Feb 13, 2007 1:51 pm
by WanamakerStudios
I have a bunch of JOIN statements for a complex search that I need to do and one of the WHERE statements requires me to have a BINARY tag attached to it so I don't get a bunch of incorrect results. I just wanted to know if there was a certain way that I should do that type of a MySQL statement ...
Posted: Tue Feb 13, 2007 2:14 pm
by Weirdan
As far as I remember, any valid expression not involving aggregate function is allowed as a join condition, i.e.
Code: Select all
select ...
from table1
inner join table2
on cast(table1.field as binary) = cast(table2.field as binary)
...
Posted: Tue Feb 13, 2007 2:20 pm
by WanamakerStudios
What exactly does CAST() do?
Posted: Tue Feb 13, 2007 2:25 pm
by Weirdan
MySQL manual wrote:
The CAST() and CONVERT() functions take a value of one type and produce a value of another type.
[...]
The BINARY operator casts the string following it to a binary string.
Code: Select all
mysql> SELECT 'a' = 'A';
-> 1
mysql> SELECT BINARY 'a' = 'A';
-> 0
mysql> SELECT 'a' = 'a ';
-> 1
mysql> SELECT BINARY 'a' = 'a ';
-> 0
[...]
BINARY
str is shorthand for CAST(
str AS BINARY).