SitePoint Sponsor

User Tag List

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

    How does one look at 2 Tables to generate a list when they have no match on a given f

    Hello,

    Say I have 2 Tables:
    T1
    T2

    And T1 has fields:
    T1_id
    submited_date
    email

    T2 has fields:

    T2_id
    email

    How do I do a Select on these 2 tables for a given date range with the condition that they do not have the same matching
    email during that date range?

    FYI I have tried this SELECT which seems like it should work, but this is not working, it is returning wrong data:

    SELECT COUNT(T1_id) FROM T2, T1
    WHERE submited_date>='2009-06-20' AND submited_date<='2009-06-24'
    AND T1.email != T2.email;


    Looking forward to your suggestions.

    Regards,

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    SitePoint Zealot
    Join Date
    Aug 2008
    Location
    NC
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure that I understand what you're trying to do. Are you trying to get all of the id's from T1 that do not match an email in T2? Or should there be a submitted date field in T2?
    From your query, I would guess that you're just getting all of the entries in T1 that match your "submitted date between" condition.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by WorldNews View Post
    How do I do a Select on these 2 tables for a given date range with the condition that they do not have the same matching
    email during that date range?
    it's not clear what you want

    please show some sample rows from each table that illustrate this scenario
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    Please see my replies interspersed:

    Quote Originally Posted by ditch182 View Post
    I'm not sure that I understand what you're trying to do. Are you trying to get all of the id's from T1 that do not match an email in T2?

    ** Yes. Get all the ids from T1 for a given date range of T1 under the Condition that the NO email address on T1 for that given date range matches an email address on T2 for the same date range.

    I guess we should also specify the same date range for T2!
    Lets call the date value for T2: date_added

    Or should there be a submitted date field in T2?

    ** Ditto.

    From your query, I would guess that you're just getting all of the entries in T1 that match your "submitted date between" condition.
    So same date range constraint of course needs to be applied to T2.

    I hope above answers clarifies completely what I need to get done.
    Looking forward to your suggestion.

    Regards,

    Anoox search engine volunteer

    www.anoox.com

  5. #5
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I posted a reply to this related question.
    I hope the additional data I provided clarifies what I want to get done.
    Looking forward to your reply.


    Quote Originally Posted by r937 View Post
    it's not clear what you want

    please show some sample rows from each table that illustrate this scenario

    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,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    please show some sample rows from each table that illustrate this scenario
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    please show some sample rows from each table that illustrate this scenario
    Hello,

    I think I have provided complete related info.
    Again we have 2 Tables:

    T1 with fields
    T1_id
    date_submitted
    email

    T2 with fields:
    T2_id
    date_added
    email

    We want to generate a SELECT which will be based on a given date range of T1 and of course matching date range of T2 while there is NO records in the results where there is matching in email in the results.

    So like:

    SELECT T2_id FORM T1, T2
    Where date_submitted < x AND date_submitted > y
    AND T1.email != T2.email

    Put another way IF a record in T1 has the same email as in T2 for that date range then do not include it in the result set.

    Regards,

    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,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i'm sorry, perhaps i should be more explicit

    you've shown the table layouts and talked about the SQL

    what i wanted to see was actual data

    table1
    42 '2009-05-29' todd@example.com
    43 '2009-06-02' fred@example.com

    table2
    104 '2009-06-02' biff@example.com

    except what i wanted you to do was construct the sample data so as to illustrate the scenario tha you want the query to return

    make sense?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi.

    So lets say:

    table1
    42 '2009-05-29' todd@example.com
    43 '2009-06-02' fred@example.com
    47, '2009-06-09' john@xyz.com
    49, '2009-06-09' jane@xyz.com

    table2
    104 '2009-06-02' biff@example.com
    99, '2009-06-10' jane@xyz.com

    Then we want to have a SELECT which returns those rows of the Table1 which have no matching emails in table2 for the given date range.
    So if the select is for date > 2009-06-02 AND < 2009-06-12

    Then the result should be ONLY:

    43 '2009-06-02' fred@example.com
    47, '2009-06-09' john@xyz.com

    since during that date jane@xyz.com is in both tables.

    Regards,

    Quote Originally Posted by r937 View Post
    i'm sorry, perhaps i should be more explicit

    you've shown the table layouts and talked about the SQL

    what i wanted to see was actual data

    table1
    42 '2009-05-29' todd@example.com
    43 '2009-06-02' fred@example.com

    table2
    104 '2009-06-02' biff@example.com

    except what i wanted you to do was construct the sample data so as to illustrate the scenario tha you want the query to return

    make sense?

    Anoox search engine volunteer

    www.anoox.com

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    strictly speaking, if the range is date > 2009-06-02 AND < 2009-06-12, then fred would not be included, right?
    Code:
    SELECT table1.T1_id
         , table1.date_submitted
         , table1.email
      FROM table1
    LEFT OUTER
      JOIN table2
        ON table2.email = table1.email
       AND table2.date_added > '2009-06-02'
       AND table2.date_added < '2009-06-12'
     WHERE table1.date_submitted > '2009-06-02'
       AND table1.date_submitted < '2009-06-12'
       AND table2.email IS NULL
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Aug 2008
    Location
    NC
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey WorldNews, if Rudy says that's right, then it's right. However, I would say that you would probably be better off combining the two tables into one. You could have one table with only two fields, email & date_submitted, where the primary key is both fields. That simplifies your query into a simple SELECT and cuts down on overhead.

  12. #12
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    With all respect I cannot see how what you are suggesting can possibly be right. I think you may have totally misunderstood what I need to do or I explained it badly.

    Specifically looking at the code below:
    We are not looking for email on T2 being NULL. In fact it cannot be NULL.
    What are we are looking for is that for the duration of the time that we are searching for that NO record on T1 should be selected which has an email address that matches an email, I repeat matches, an email address on T1.

    Regards,

    Quote Originally Posted by r937 View Post
    strictly speaking, if the range is date > 2009-06-02 AND < 2009-06-12, then fred would not be included, right?
    Code:
    SELECT table1.T1_id
         , table1.date_submitted
         , table1.email
      FROM table1
    LEFT OUTER
      JOIN table2
        ON table2.email = table1.email
       AND table2.date_added > '2009-06-02'
       AND table2.date_added < '2009-06-12'
     WHERE table1.date_submitted > '2009-06-02'
       AND table1.date_submitted < '2009-06-12'
       AND table2.email IS NULL

    Anoox search engine volunteer

    www.anoox.com

  13. #13
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    These Tables are actually huge in number of columns in each AND they are for totally different purposes, so they cannot be combined.
    But thanx for the suggestion.

    Quote Originally Posted by ditch182 View Post
    Hey WorldNews, if Rudy says that's right, then it's right. However, I would say that you would probably be better off combining the two tables into one. You could have one table with only two fields, email & date_submitted, where the primary key is both fields. That simplifies your query into a simple SELECT and cuts down on overhead.

    Anoox search engine volunteer

    www.anoox.com

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by WorldNews View Post
    With all respect I cannot see how what you are suggesting can possibly be right.
    it's a LEFT OUTER JOIN with an IS NULL test, a classic query structure

    it returns only unmatched rows from the left table (that's the IS NULL part)

    that's how a LEFT OUTER JOIN works

    did you test it?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    Actually after some tweaking, it does work!
    I guess I do not really understand the (magic) of this LEFT OUTER JOIN!
    I mean I look and look at this code and cannot see how it can work given that it seems to be doing exactly opposite of what I need. That is, it is
    checking for matching emails between these two tables where we want the exact opposite of it!

    I guess you must be the King of MySQL LEFT OUTER JOIN

    In seriousness, do you have a paper (book, write up, web site, etc.) about LEFT OUTER JOIN that you recommend reading to better understand the working and power of this feature?

    Regards,

    Quote Originally Posted by r937 View Post
    it's a LEFT OUTER JOIN with an IS NULL test, a classic query structure

    it returns only unmatched rows from the left table (that's the IS NULL part)

    that's how a LEFT OUTER JOIN works

    did you test it?

    Anoox search engine volunteer

    www.anoox.com

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by WorldNews View Post
    In seriousness, do you have a paper (book, write up, web site, etc.) about LEFT OUTER JOIN that you recommend reading to better understand the working and power of this feature?
    try chapter 3 of my book, which you can read here --> http://www.sitepoint.com/article/sim...e-from-clause/

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Great, ThanX.

    Actually I think I will just buy your Book

    Couple of questions about your Book:
    1- Does it cover MySQL Admin issues too?
    2- It is very example driven rather than being lecture like?
    I mean does it teach by actually writing & executing code?

    Regards,

    Quote Originally Posted by r937 View Post
    try chapter 3 of my book, which you can read here --> http://www.sitepoint.com/article/sim...e-from-clause/


    Anoox search engine volunteer

    www.anoox.com

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    1- Does it cover MySQL Admin issues too?

    none whatsoever


    2- It is very example driven rather than being lecture like?

    you betcha

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •