List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:February 7 2011 4:16pm
Subject:Re: Finding Data in One of Two Tables
View as plain text  
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
Thread
Finding Data in One of Two TablesHal Vaughan2 Feb
  • Re: Finding Data in One of Two TablesMySQL)7 Feb