worldnews — 2013-11-20T10:27:09-05:00 — #1
We have 2 Tables, lets call them: swd & swi
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:
FROM swd, swi
WHERE submited_date > (DATE_SUB(CURDATE(), INTERVAL 30 DAY))
AND approved_date IS NOT NULL
AND swi.id = swd.ix_id;
But how does one show the reverse?
guido2004 — 2013-11-20T10:33:42-05:00 — #2
worldnews — 2013-11-20T11:09:16-05:00 — #3
I tried a bunch of LEFT JOIN SELECT statements, and did not work.
Can you kindly type out the correct SELECT for above described list?
guido2004 — 2013-11-20T12:12:34-05:00 — #4
Why don't you post yours, and we'll tell you where you went wrong
BTW: 1000 posts! Way to go! :Partydude:
worldnews — 2013-11-20T17:52:36-05:00 — #5
I tried this:
SELECT usr.user_id, email, signup_date
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
r937 — 2013-11-20T22:19:27-05:00 — #6
because you're doing a cross join
in your ON clause, you're joining each user to all the ccl rows for all other users
try this --
ON ccl.user_id <font color='"#FF0000"'>[B]=[/B]</font> usr.user_id
WHERE usr.sign_update < CURRENT_DATE - INTERVAL 30 DAY
AND <font color='"#FF0000"'>ccl.user_id IS NULL</font>
BY usr.user_id DESC
worldnews — 2013-11-21T10:10:04-05:00 — #7
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.
And thanks again for this code sugg.
r937 — 2013-11-21T13:36:55-05:00 — #8
thanks for the feedback
perhaps give it another chance?
i've received nothing but positive reviews about how easy it is to understand
JOINs are covered in quite a lot of detail on pages 37 to 64
worldnews — 2013-11-21T16:31:54-05:00 — #9
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.