jkkenzie Posted February 25, 2012 Share Posted February 25, 2012 There is a record (tbl_business.appformno LIKE '%3133%' ) which actually exists but there is no Record for it on the table tbl_activities.appformno: The following search does not show the above record, yet it should show because am using LEFT JOIN SELECT tbl_business.id AS `ID` , tbl_business.appformno AS `Form No` , tbl_business.bid AS `BID` , tbl_business.company AS `Business Name` , tbl_business.pobox AS `Address` , tbl_business.postalcode AS `Postal Code` , tbl_business.plotno AS `Plot No.` , tbl_business.zonecode AS `Zone Name/Code` , tbl_activities.act_code AS `Activity Code` , tbl_business.ne AS `N/E` FROM tbl_business LEFT JOIN tbl_activities ON tbl_business.appformno = tbl_activities.appformno WHERE tbl_business.appformno LIKE '%3133%' AND tbl_business.bid LIKE '%%' AND `tbl_business`.`contperson` LIKE '%%' AND `tbl_business`.`zonecode` LIKE '%%' AND `tbl_activities`.`act_code` LIKE '%%' AND `tbl_business`.`roadstreet` LIKE '%%' AND `tbl_business`.`company` LIKE '%%' AND `tbl_business`.`building` LIKE '%%' ORDER BY `tbl_business`.`appformno` ASC LIMIT 100 Link to comment https://forums.phpfreaks.com/topic/257756-left-join-not-working-well-with-where-clause/ Share on other sites More sharing options...
requinix Posted February 25, 2012 Share Posted February 25, 2012 The LEFTness only influences the join condition - if a WHERE condition excludes something, it'll be excluded. Does it show up if you drop all the worthless LIKE '%%' conditions? Link to comment https://forums.phpfreaks.com/topic/257756-left-join-not-working-well-with-where-clause/#findComment-1321098 Share on other sites More sharing options...
jkkenzie Posted February 25, 2012 Author Share Posted February 25, 2012 Yes it works! Though what if there was a value entered for the worthless LIKE ='%%' how do you normally achieve that. Link to comment https://forums.phpfreaks.com/topic/257756-left-join-not-working-well-with-where-clause/#findComment-1321103 Share on other sites More sharing options...
jkkenzie Posted February 25, 2012 Author Share Posted February 25, 2012 Omitting the Line: AND `tbl_activities`.`act_code` LIKE '%%' Solves the problem Since i know the record doesn't have a related record on the second table, why should i match with LIKE. I will have to have another button for searching such records since on a search that includes AND `tbl_activities`.`act_code` LIKE '%%' will NOT show records without a matching record on the second table(tbl_activities) OR what do you think:? Link to comment https://forums.phpfreaks.com/topic/257756-left-join-not-working-well-with-where-clause/#findComment-1321108 Share on other sites More sharing options...
requinix Posted February 25, 2012 Share Posted February 25, 2012 Only include the conditions if you actually need them. // multiple statements $query = "SELECT ..."; if ($condition) $query .= " AND table.field LIKE '%foo%'"; // one big query $query = " SELECT ... " . ($condition ? "AND table.field LIKE '%foo%'" : "") . " Link to comment https://forums.phpfreaks.com/topic/257756-left-join-not-working-well-with-where-clause/#findComment-1321112 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.