SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Left join query is producing unexpected results

    Im a bit confused why I am getting rows where senMatches = No and lastMatch = '2012-09-27. Any thoughts?

    Thanks!


    SELECT
    u.uID,
    u.firstName,
    u.lastName,
    u.email,
    u.sendMatches,
    date_format(u.lastMatch, '%m-%d-%Y') as lastMatch,
    s.sID,
    date_format(s.searchDate, '%m-%d-%Y') as searchDate,
    s.uID,
    s.type,
    s.keyword,
    s.alerts,
    date_format(s.lastAlert, '%m-%d-%Y') as lastAlert,
    s.clicks,
    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')
    GROUP BY
    u.uID
    ORDER BY
    s.searchDate
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you, that did the trick!
    Convert your dollars into silver coins. www.convert2silver.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •