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).