Finding Data in One of Two Tables

I'm using this query in a Perl program:

SELECT Distinct x.Search FROM $source.Searches AS x LEFT JOIN =
searching.Status AS s=20
ON x.Search=3Ds.Search AND s.Source=3D'$source' WHERE =
x.RedoTime<'$now' AND s.Search IS NULL

This program runs other programs that do internet searches. I have =
different sources (stored in $source, of course), and source has it's =
own DB with a table, "Searches." Each row of searches describes a =
different search that can be done and each search has a name, which is =
stored in the Searches column (within the Searches table, so, yes, I use =
that name for a table and a column).

When a search is being executed, an entry is placed in searching.Status, =
with one row in that table showing the status of the search.

So if I have a source named "alpha" and searches named "one" and "two" =
and the system is executing the search "one," not only is there a row in =
alpha.Searches describing "one" in depth, but there is a row in =
searching.Status describing the progress with "one."

When each search is done, the RedoTime is set so it's easy to see when =
it needs to be executed again.

What I want to do is get a list of searches in the Searches table =
(within the source's DB) that are NOT listed in Status and where the =
RedoTime is before $now (the current time).

=46rom what I've read, the query above should do it, but I have this =
nagging feeling I've done something wrong.

Will that query pick up all rows in $source.Searches that have a =
RedoTime before $now that are NOT also listed in searching.Status?


Thanks for any help on this!




Hal=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Finding Data in One of Two Tables

On 2/2/2011 04:23, Hal Vaughan wrote:
> I'm using this query in a Perl program:
>
> SELECT Distinct x.Search FROM $source.Searches AS x LEFT JOIN searching.Status AS s
> ON x.Search=s.Search AND s.Source='$source' WHERE x.RedoTime<'$now' AND s.Search IS NULL
>
> This program runs other programs that do internet searches. I have different sources (stored in $source, of course), and source has it's own DB with a table, "Searches." Each row of searches describes a different search that can be done and each search has a name, which is stored in the Searches column (within the Searches table, so, yes, I use that name for a table and a column).
>
> When a search is being executed, an entry is placed in searching.Status, with one row in that table showing the status of the search.
>
> So if I have a source named "alpha" and searches named "one" and "two" and the system is executing the search "one," not only is there a row in alpha.Searches describing "one" in depth, but there is a row in searching.Status describing the progress with "one."
>
> When each search is done, the RedoTime is set so it's easy to see when it needs to be executed again.
>
> What I want to do is get a list of searches in the Searches table (within the source's DB) that are NOT listed in Status and where the RedoTime is before $now (the current time).
>
> From what I've read, the query above should do it, but I have this nagging feeling I've done something wrong.
>
> Will that query pick up all rows in $source.Searches that have a RedoTime before $now that are NOT also listed in searching.Status?
>
>
> Thanks for any help on this!
>

Maybe today isn't my day. I can't distinguish between what you are
looking for and what you do not want to find.

WANTED:
a list of searches in the Searches table (within the source's DB) that
are NOT listed in Status and where the RedoTime is before $now (the
current time).

NOT WANTED:
rows in $source.Searches that have a RedoTime before $now that are NOT
also listed in searching.Status

I can't seem to spot the difference. Maybe if you phrased it differently
or provided two or three sample rows for each table I could understand
your distinction?

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

The Customer Support Center Will Retire February 11, 2011
Find out what you need to know about the migration to My Oracle Support:
http://www.oracle.com/us/support/mos-mysql-297243.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org