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

[WorldNews]

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,

[ditch182]

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.

[r937]

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

[WorldNews]

Hello,

Please see my replies interspersed:

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,

[WorldNews]

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.

it's not clear what you want

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

[r937]

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

[WorldNews]

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,

[r937]

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?

[WorldNews]

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,

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]

strictly speaking, if the range is date > 2009-06-02 AND < 2009-06-12, then fred would not be included, right?

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

[ditch182]

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.

[WorldNews]

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,

strictly speaking, if the range is date > 2009-06-02 AND < 2009-06-12, then fred would not be included, right?

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

[WorldNews]

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.

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.

[r937]

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?

[WorldNews]

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,

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]

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/

[WorldNews]

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,

try chapter 3 of my book, which you can read here --> http://www.sitepoint.com/article/sim...e-from-clause/

[r937]

1- Does it cover MySQL Admin issues too?

none whatsoever


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

you betcha