title: MySQL LEFT JOIN Query with WHERE clause
body: <p>Hope someone can help as I am having a hard time understanding how to query properly</p>
  
  <p>I have a Member table and a Member_Card table. Member_Card has a column Member, so the card is associated to a member. Both tables have a LastModifiedDate column. A member can have none, one or several cards.</p>
  
  <p>I need to return all members whose LastModifiedDate >= sinceDate (given date) OR whose card (if any) LastModifiedDate >= sinceDate.</p>
  
  <p>Imagine sinceDate is 2013-01-01 00:00:00. I want to output something like this:</p>
  
  <pre><code>[{
      "Id": "001O000000FsAs7IAF",
      "LastModifiedDate": 2013-01-01 00:00:00,
      "Member_Card": null
  }, {
      "Id": "001O000000FrpIXIAZ",
      "LastModifiedDate": 2012-12-12 00:00:00,
  
      "Member_Card": [{
          "Id": "a00O0000002w8FGIAY",
          "Member": "001O000000FhDSoIAN",
          "LastModifiedDate": 2013-01-01 00:00:00
  
      }, {
          "Id": "a00O0000002uYMtIAM",
          "Member": "001O000000FhDSoIAN",
          "LastModifiedDate": 2012-12-12 00:00:00
      }]
  }, {
      "Id": "001O000000FsAg7IAF",
      "LastModifiedDate": 2013-01-01 00:00:00,
      "Member_Card": [{
          "Id": "a00O0000002w8FFIAY",
          "Member": "001O000000FhDSoIAN",
          "LastModifiedDate": 2012-12-12 00:00:00
      }]
  }]
  </code></pre>
  
  <p>The 1st is a member with a matching LastModifiedDate without cards. The 2nd is a member with a non-matching LastModifiedDate, but he has 2 cards associated, and 1 of them has a matching LastModifiedDate. The 3rd one is a member with a matching LastModifiedDate with a card.</p>
  
  <p>Thanks to SO I got the following query:</p>
  
  <pre><code>SELECT member.*,card.* from member inner join (
  SELECT distinct member.id as  mid FROM Member 
  INNER JOIN Member_Card 
      ON Member_Card.Member = Member.id 
  and ( Member.LastModifiedDate &gt;= sinceDate  
      OR Member_Card.LastModifiedDate &gt;= sinceDate ) ) a on a.mid=member.id
  inner join member_card card on card.member=a.mid
  </code></pre>
  
  <p>Which works fine but is missing the case where the member doesn't have any card associated.</p>
  
  <p>I tried to change some INNER JOINs to LEFT JOINs but then it's ignoring the date comparison :-(</p>
  
  <p>Can you help me with this one?</p>