MySQL LEFT JOIN using a Subquery

[beriahthebee]

I have blown a few brain cells trying to think about how to do this. I am hoping that I can borrow all of yours for a few minutes.

I have a couple tables which hold information about members and thier appoints with instructors in a couple different tables. I eventually want to be able to enter a date and find all members that have not checked in for an appointment since the entered date. Right now I am trying to get mysql to atleast spit be back from one of those tables a list of members that have NEVER checked in. I wrote this query thinking that I had it licked.

SELECT
  tblM.idMember
FROM
  tblMembers AS tblM
LEFT JOIN
  (
    SELECT
      tblDTA.idMember AS idMember
    FROM
      tblDuoTrioAssignments AS tblDTA
    LEFT JOIN
      tblDuoTrioSchedules AS tblDTS
      ON tblDTS.idSchedule=tblDTA.idSchedule
    WHERE
      tblDTS.intStatus=1
      AND UNIX_TIMESTAMP( STR_TO_DATE( CONCAT( tblDTS.intYear, '-', tblDTS.intMonth, '-', tblDTS.intDay ), '%Y-%m-%d' ) )>1
      AND tblDTA.intStatus=2
  ) AS tmpDuoTrioFilter
 ON tmpDuoTrioFilter.idMember=tblM.idMember
WHERE
  tblM.idMember IS NULL

The error I get back is this....

Unknown column 'tblM.idMembe' in 'field list'

First off, its obiviously tblM.idMember. Why did it drop off the r?
Second, the column exists. Please don't post questions asking me if that column exists. This isnt my first time in a soup of queries.

Any ideas why this doesnt work?

[cbj4074]

This is probably irrelevant, but have you tried enclosing your field names in backticks? I've seen instances in which field names were inexplicably defaced in the absence of backticks.

[tomhath]

The first thing I'd do is type the query in again. Don't copy/paste, retype it. There might be some strange non-displayable character somewhere.

If that doesn't help, I can't offer any more advice. Sorry.

[crazyjeremy]

If you have shell access, try the query via mysql command line. Or try it through a 3rd party interface if available (like phpmyadmin).

[jheitz]

It might be my brain cells not working either, but there is something odd about your query (not that is related to your problem, but makes me doubt it will provide you with the expected data):

You are selecting ID Members from the Members table that match certain criteria (your subselect, sorry, I haven't looked at it too closely) AND THE ID MEMBER IS NULL

It doesn't look as if you are going to get many ID Members back... (?). Maybe I missed something, sorry if that is the case...

[tsinka]

Hi,

I think the WHERE clause needs to be corrected. You need to check if the idMember field in tmpDuoTrioFilter is NULL:

ON tblM.idMember = tmpDuoTrioFilter.idMember
WHERE
tmpDuoTrioFilter.idMember IS NULL

Regards,
Thomas