busboy — 2012-09-27T20:57:37-04:00 — #1
I’m a bit confused why I am getting rows where senMatches = ‘No’ and lastMatch = '2012-09-27’. Any thoughts?
date_format(u.lastMatch, '%m-%d-%Y') as lastMatch,
date_format(s.searchDate, '%m-%d-%Y') as searchDate,
date_format(s.lastAlert, '%m-%d-%Y') as lastAlert,
date_format(s.lastClick, '%m-%d-%Y') as lastClick,
date_format(u.lastLogin, '%m-%d-%Y') as lastLogin
FROM searches s
LEFT JOIN users u
ON s.uID = u.uID
WHERE MATCH (keyword) AGAINST ('sport' in boolean mode) or keyword in ('')
and u.sendMatches = 'Yes'
and u.bouncing = 'No'
and date(u.lastMatch) <> '2012-09-27'
and s.type in ('basic', 'advanced')
guelphdad — 2012-09-27T21:58:04-04:00 — #2
You have a problem with AND and OR clauses.
wrap this in brackets instead:
WHERE (MATCH (keyword) AGAINST ('sport' in boolean mode) or keyword in (''))
actually this wasn't right but is important as well:
when writing a LEFT JOIN any conditions on the right hand table belong in the LEFT JOIN clause and not in the WHERE clause. Putting them in the WHERE clause effectively changes your join to an INNER JOIN because it discards all the rows that are null that don't match the where clause. So move conditions on the Users table into the join with an AND and put those conditions before the WHERE.
busboy — 2012-09-28T13:49:51-04:00 — #3
Thank you, that did the trick!
mittineague — 2014-09-18T22:21:32-04:00 — #4
This topic is now archived. It is frozen and cannot be changed in any way.