mysql left outer join returns all rows, ignores specified field value -
i have 2 tables in mysql database:
cellphone table id phone_number verification_code table id verification_codes code_expires code_used
i'm trying query specific id in cellphone table, , want row whether or not have valid verification code.
here query
select a.id, a.phone_number, b.verification_code, b.code_expires cellphone left outer join verification_codes b on (a.id = b.id , a.id = '12345' , b.code_expires > now() , b.code_used null)
instead of getting id i'm looking for, huge recordset includes id numbers. can me format query correctly?
conditions on first table in left join
go in where
clause. conditions in second go in on
:
select c.id, c.phone_number, vc.verification_code, vc.code_expires cellphone c left outer join verification_codes vc on c.id = vc.id , vc.code_expires > now() , vc.code_used null c.id = '12345'; -- single quotes unnecessary if id number
the reason simple. left join
keeps all rows in first table regardless of whether on
clause evaluates true, false, or null
. true conditions on first table. hence, conditions on first table of left join
ignored when in on
clause.
Comments
Post a Comment