Select recs from table 1 that arent in table 2 1

[PulsaMatt]

I know how to do this with a sub-select ... but I am leary to install the alpha version of mySQL 4.1 on a production server (currently using 4.0.13) ... anywho ...

I need to be able to find the records that exist in Table 1, but do not exist in table 2 (they share a common key). Any ideas? I have tried everything I can think of (which isnt much I admit, self-teaching myself SQL).

Example I have tried:

select rmaNum, docType, custName, product, recDate from jdeoutput, rmatrack jdeoutput.rmaNum != rmatrack.rmaNumber (This query returns 24000 records, but only 200 exist at the moment).

Anyhelp would be greatly appreciated.

Matt Laski
Netadmin, Pulsafeeder Inc.

 

[sleipnir214]

This is a job for LEFT JOIN.

Assuming you have some unique record ID that uniquely identifies a record in a table, but that can be found in both tables:

SELECT
*
FROM
table1
left join
table2
on
table1.uniquefield = table2.uniquefield where
table2.uniquefield is null

will find all columns from all records in table1 that do not exist in table2

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[PulsaMatt]

That did it! THANKS!!!!!!!

Matt Laski
Netadmin, Pulsafeeder Inc.