Binary Select In Join Statement

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Binary Select In Join Statement

Post by WanamakerStudios »

Anyone know how to do a BINARY select in a JOIN statement?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post 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 ...
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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)
...
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post by WanamakerStudios »

What exactly does CAST() do?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

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