SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)

  3. #3
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

    Anoox search engine volunteer

    www.anoox.com

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Why don't you post yours, and we'll tell you where you went wrong

    BTW: 1000 posts! Way to go!

  5. #5
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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
    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

    Anoox search engine volunteer

    www.anoox.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    But it killed the MySQL server
    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 --
    Code:
    SELECT usr.user_id
         , usr.email
         , usr.signup_date
      FROM usr
    LEFT OUTER
      JOIN ccl
        ON ccl.user_id = usr.user_id
     WHERE usr.sign_update < CURRENT_DATE - INTERVAL 30 DAY
       AND ccl.user_id IS NULL
    ORDER 
        BY usr.user_id DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

    Quote Originally Posted by r937 View Post
    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 --
    Code:
    SELECT usr.user_id
         , usr.email
         , usr.signup_date
      FROM usr
    LEFT OUTER
      JOIN ccl
        ON ccl.user_id = usr.user_id
     WHERE usr.sign_update < CURRENT_DATE - INTERVAL 30 DAY
       AND ccl.user_id IS NULL
    ORDER 
        BY usr.user_id DESC

    Anoox search engine volunteer

    www.anoox.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    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.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.


    Quote Originally Posted by r937 View Post
    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

    Anoox search engine volunteer

    www.anoox.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •