En Query ing Minds Want To know.

timstring

I am trying to compare a mammoth table with over 300,000 rows and a much smaller table to see if data is in the big one and not in the little one. Here's my basic idea:

select TOAWorkOrdersNew.TechNum AS TOATechNum,
TOAWorkOrdersNew.FirstName AS TOAFirst,
TOAWorkOrdersNew.LastName AS TOALast,
TOAWorkOrdersNew.Title AS TOATitle,
TOAWorkOrdersNew.WorkDate AS TOAWorkDate,
Techs.TechNum AS TechNum,
Techs.FirstName AS FirstName,
Techs.LastName AS LastName,
Techs.JobDesc AS Job

from TOAWorkOrdersNew, Techs

Where 

!!!!!    TOAWorkOrdersNew.WorkDate between '2013-05-15' and '2013-05-31' AND
 TOAWorkOrdersNew.TechNum [U]IS NOT FOUND INSIDE[/U] Techs.TechNum    !!!

Techs.TechNum is the unique key for Techs.

Thanks as always for the help,
tim

Weedpacket

I was going to suggest using SQL's set EXCEPT operator for that, but then I remembered that MySQL doesn't support it.

Select (primary key) FROM big_table WHERE (primary key) NOT IN (Select (primary key) FROM small_table)

NogDog

timstring;11029231 wrote:
...a mammoth table with over 300,000 rows...

Pffft: I'm running a script right now that includes a table with 55,000,000 rows. 😉 :p

Bonesnap

NogDog;11029249 wrote:
Pffft: I'm running a script right now that includes a table with 55,000,000 rows. 😉 :p

Pffft: I have several scripts using a database that has over 641 million rows (and growing!). :p

EDIT: Actually I just remembered that I emptied my database and started again so it's only slightly above (a paltry) 300 million rows (but still growing!). 🙂

NogDog

Bonesnap;11029337 wrote:
Pffft: I have several scripts using a database that has over 641 million rows (and growing!). :p

EDIT: Actually I just remembered that I emptied my database and started again so it's only slightly above (a paltry) 300 million rows (but still growing!). 🙂

Okay, you got me, but our data only grows at maybe half a million rows per month for that table.

Bonesnap

NogDog;11029345 wrote:
Okay, you got me, but our data only grows at maybe half a million rows per month for that table.

Mine is just a simple hash database, so your table is probably more complicated. With a number of scripts I have running it increases by several million per day but it gets slower as more rows are added since I have an "INSERT IGNORE" on the queries.