How do I generate a list that shows what is in 1 Table and not other

Databases
1

Hello,

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:

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;

But how does one show the reverse?

Thanks

2

Using LEFT JOIN http://dev.mysql.com/doc/refman/5.5/en/join.html

3

Hi,

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?

Thanks.

4

Why don’t you post yours, and we’ll tell you where you went wrong :wink:

BTW: 1000 posts! Way to go! :Partydude:

5

Hi,

I tried this:

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 :frowning:
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

Thanks

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 –

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
7

Hello,

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.

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

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 :slight_smile:

But thanks again, for the suggestion about this Question.