在php中以不同的方式加入两个mysql表

I have following mysql tables.

1. members
----------
|id |name|
----------
|001|aaa |
|002|bbb |
|003|ccc |
|004|ddd |
----------

2. membershipfees
----------------
|id |date      |
----------------
|001|2012-01-01|
|002|2012-01-01|
----------------

I want to show list of the members who not paid the fees for the current month. My php code is,

SELECT Members.id, Members.Name FROM Members
    LEFT JOIN MembershipFees
    ON Members.id = MembersipFees.id
    WHERE DATE_FORMAT(MembershipFees.Date, '%Y%m') <> DATE_FORMAT(NOW(), '%Y%m')
    ORDER BY Members.id ASC

But it shows 0 rows. Any suggestions please.

You need to put MembersipFees.id IS NULL condition is where clause. Also if date field is varchar column then you should be using STR_TO_DATE function.

SELECT Members.id, Members.Name
FROM Members
     LEFT JOIN membershipfees
        ON Members.id = membershipfees.id
           AND DATE_FORMAT(STR_TO_DATE(membershipfees.`Date`, '%d-%m-%Y'), '%Y%m') = 
                           DATE_FORMAT(NOW(), '%Y%m')
WHERE membershipfees.id IS NULL
ORDER BY Members.id ASC;

SEE SQLFIDDLE DEMO HERE

I can't test it right now, but please try and let me know if it works for you:

SELECT m.id, m.Name FROM Members m
Where not exists 
    (select 1 from membershipfees mm 
     where 
     mm.Date > DATE_FORMAT(NOW(), '%Y-%m')
     and mm.id = m.id)

UPDATE:
It works for me

try this:

You Just have to add membershipfees.id IS NULL condition to your query

SELECT members.id, members.Name  
FROM   members 
LEFT outer JOIN membershipfees 
ON       members.id = membershipfees.id 
and    DATE_FORMAT(membershipfees.Date, '%Y%m')
                          <> DATE_FORMAT(NOW(), '%Y%m') 
where  membershipfees.id IS NULL
ORDER BY members.id ASC

Try this query..

SELECT Members.id, Members.Name FROM Members where id not in (select id from membershipfees WHERE DATE_FORMAT(MembershipFees.Date, '%Y%m') <> DATE_FORMAT(NOW(), '%Y%m') ORDER BY Members.id ASC)