LEFT JOIN not working well with WHERE clause

[jkkenzie]

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 

[requinix]

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?

[jkkenzie]

Yes it works! Though what if there was a value entered for the worthless LIKE ='%%' how do you normally achieve that.

[jkkenzie]

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:?

[requinix]

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%'" : "") . "