MySQL LEFT JOIN Query with WHERE clause

Question

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?

Jim · Accepted Answer · 2013-08-08 10:42:50Z

4

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 ) 
)

edited Aug 8, 2013 at 10:42

answered Aug 8, 2013 at 10:16

Jim

22.7k6 gold badges55 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Daniel W. Over a year ago

Be careful with subselect in IN(). Even simple queries take hours on many MySQL Versions. Use EXISTS() instead.

Daniel W. Over a year ago

You forgot a closing ) :-)

Ayyappan Sekar · Accepted Answer · 2013-08-08 10:19:29Z

0

Try this... (yet to be tested)

SELECT * FROM member m1
JOIN card c1 ON c1.member = m1.id
WHERE m1.id IN (
SELECT DISTINCT m.id from member m
JOIN card c ON c.member = m.id
WHERE (c.LastModifiedDate >=sinceDate  OR m.LastModifiedDate >= sinceDate))

answered Aug 8, 2013 at 10:19

Ayyappan Sekar

11.5k2 gold badges22 silver badges22 bronze badges

2 Comments

momo Over a year ago

thanks for helping but your query has the same problem of not showing members without cards

mickmackusa Over a year ago

Please never post code-only answers on Stack Overflow. This is very unhelpful to the OP, researchers, and all future askers who have their question closed by this page.

Praveen · Accepted Answer · 2015-03-25 08:05:52Z

0

You can try like this,

SELECT o.status, o.date, c.name
FROM cliente c
LEFT JOIN orders o
ON c.id=o.id 
WHERE o.date = '2/1/2015'

answered Mar 25, 2015 at 8:05

Praveen

1,8913 gold badges21 silver badges36 bronze badges

1 Comment

mickmackusa Over a year ago

Please never post code-only answers on Stack Overflow. This is very unhelpful to the OP, researchers, and all future askers who have their question closed by this page.

Collectives™ on Stack Overflow

MySQL LEFT JOIN Query with WHERE clause

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related