mysql中where后left,使用WHERE子句的MySQL LEFT JOIN查询

Hope someone can help as I am having a hard time understanding how to query properly

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.

I need to return all members whose LastModifiedDate >= sinceDate (given date) OR whose card (if any) LastModifiedDate >= sinceDate.

Imagine sinceDate is 2013-01-01 00:00:00. I want to output something like this:

[{

"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

}]

}]

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.

Thanks to SO I got the following query:

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 >= sinceDate

OR Member_Card.LastModifiedDate >= sinceDate ) ) a on a.mid=member.id

inner join member_card card on card.member=a.mid

Which works fine but is missing the case where the member doesn't have any card associated.

I tried to change some INNER JOINs to LEFT JOINs but then it's ignoring the date comparison :-(

Can you help me with this one?

解决方案

You want to move the date check to the where of the inner query.

Try something like:

SELECT member.*,card.*

FROM member

LEFT JOIN member_card card on card.member=member.id

WHERE member.id IN (

SELECT DISTINCT m.id FROM Member m

LEFT JOIN Member_Card mc ON (mc.Member = m.id)

WHERE ( m.LastModifiedDate >= sinceDate

OR mc.LastModifiedDate >= sinceDate )

)