how does one get a set of items that are in one table but not inanother?

[Lawrence Krubner]

I hope this isn't already in some FAQ. (I did go to the MySql website
and search for "FAQ", but all I got was this:
http://search.mysql.com/search?q=faq.html&lr=lang_en)

Assume I have two database tables:

ftp_uploads
import_queue

One keeps track of all FTP uploads to a server (a cron script harvests
data out of the ftp logs and stores it in a database).

The other keeps track of meta data that needs to be stored in the
database, related to those FTP uploads.

So stuff gets uploaded to the database, then it needs to be imported to
our farm of NAS-connected boxes. All the servers run RedHat Linux. We
are using the PID (which the server assigns to each FTP upload) to link
the files in ftp_uploads with the data in import_queue. Every PID that
gets input into ftp_uploads should also appear in import_queue. However,
because of a programming error, we ended up with a few hundred entries
in ftp_uploads that were not also in import_queue. I'd like to delete
some of the entries.

My question: How do I find the rows in ftp_uploads that do not have a
matching PID in import_queue? The simplest thing seems like:

SELECT ftp_uploads.id
FROM ftp_uploads, import_queue
WHERE ftp_uploads.pid != import_queue.pi d

But of course, that simply gives me the Cartesian product of all the
rows that match.




-- lawrence

[Thomas 'PointedEars' Lahn]

Re: how does one get a set of items that are in one table but notin another?

Lawrence Krubner wrote:

I hope this isn't already in some FAQ. (I did go to the MySql website
and search for "FAQ", but all I got was this:
http://search.mysql.com/search?q=faq.html&lr=lang_en)
[...]
My question: How do I find the rows in ftp_uploads that do not have a
matching PID in import_queue? The simplest thing seems like:
>
SELECT ftp_uploads.id
FROM ftp_uploads, import_queue
WHERE ftp_uploads.pid != import_queue.pi d
>
But of course, that simply gives me the Cartesian product of all the
rows that match.

I presume that *is* a FAQ since the MySQL documentation has it as an
example. Read on JOIN and ask further questions about it where MySQL
is on-topic:

comp.databases. mysql

See <http://jibbering.com/faq/#FAQ2_3>.


PointedEars
--
Anyone who slaps a 'this page is best viewed with Browser X' label on
a Web page appears to be yearning for the bad old days, before the Web,
when you had very little chance of reading a document written on another
computer, another word processor, or another network. -- Tim Berners-Lee