How does one generate a list that would say:
Show me all entries in swi added in last 30 days whom do not have their
id entered as matching key in swd?
We can generate the reverse of this list easy, that is show those ids in
swi that do have an entry in swd, via this:
SELECT COUNT(swi.id)
FROM swd, swi
WHERE submited_date > (DATE_SUB(CURDATE(), INTERVAL 30 DAY))
AND approved_date IS NOT NULL
AND swi.id = swd.ix_id;
SELECT usr.user_id, email, signup_date
FROM usr
LEFT JOIN ccl
ON usr.user_id != ccl.user_id
WHERE usr.sign_update < (DATE_SUB(CURDATE(), INTERVAL 30 DAY))
ORDER BY usr.user_id DESC
But it killed the MySQL server
So just to be clear: we want all users from usr Table, for the last X days, whom did not complete the entry that goes into the ccl Table
in your ON clause, you’re joining each user to all the ccl rows for all other users
try this –
SELECT usr.user_id
, usr.email
, usr.signup_date
FROM usr
LEFT OUTER
JOIN ccl
ON ccl.user_id [COLOR="#FF0000"][B]=[/B][/COLOR] usr.user_id
WHERE usr.sign_update < CURRENT_DATE - INTERVAL 30 DAY
AND [COLOR="#FF0000"]ccl.user_id IS NULL[/COLOR]
ORDER
BY usr.user_id DESC
Your code is correct. Thanks.
Man this JOIN code is something that I need to learn much better, any good read about this you can suggest that is to the point and
real world and easy to follow? BTW, we do have your SQL book on our shelves but the section it has about JOIN does not do a
good job of describing this incredible and nifty part of SQL well at all. So if you have another suggestion or a new better book
focused on JOIN please let us know.
Really, I have read that section a few times and it is just not clear about how best to use the powerful JOIN. Specially the examples in there about JOIN is so not useful, so far from even a most basic real world Table, that one cannot even follow the example to figure it out!
Maybe you should write a book just about JOIN. We will buy a few copies 4 sure
But thanks again, for the suggestion about this Question.