Your question seems to have been answered already, so let me add one
cautionary note -- use a LIMIT clause when testing out a new query!
Especially with a join. A simple mistake can lead to a Cartesian
product of 2 tables -- I always do limit 100 or something so that I
can then check to see that
Obviously you can do a join when the names of id columns are different as
well. Look in the doc for that. This has nothing to do with your problem of
finding rows not in another table - it is a basic sql thing.
Stefan
Am Sunday 26 March 2006 00:47 schrieb barney:
Thanks, Stefan,
But that
Folk,
This may be off-base for this list, but I've run out of places to look, sorry.
I can't seem to find this anywhere, although I'm certain I've seen it before.
How can I identify all the records in a table that are not referenced in a
many-to-many table?
I have a [unique] table of files
Use
select first_table.id from first_table left join second_table using (id) where
second_table.id is null
Stefan
Am Saturday 25 March 2006 19:10 schrieb barney:
Folk,
This may be off-base for this list, but I've run out of places to look,
sorry. I can't seem to find this anywhere,
Thanks, Stefan,
But that only works if both tables have the same field name, doesn't it? If I
use
select FileKey from dl_files left join dl_merges using (FileID) where FileID is
null
MySQL returns
Unknown column 'articles.dl_files.FileID' in 'on clause'.
Correct me if I'm wrong, but wouldn't
barney
Correct me if I'm wrong, but wouldn't I have to modify the column name
in one of the tables in order for that to work? Or is there a syntax
in the join lexicon that I can use to alias one of the column names
within the query? I can't modify the existing table structure(s).
If the
barney wrote:
Thanks, Stefan,
But that only works if both tables have the same field name, doesn't it? If I
use select FileKey from dl_files left join dl_merges using (FileID) where
FileID is null MySQL returns
Unknown column 'articles.dl_files.FileID' in 'on clause'.
Correct me if I'm wrong,
I wish!
That's the first thing that occurred to me ... unfortunately, this must be done
by query ... I'm not allowed to create tables sigh /. Seems the guy I'm
doin' this for doesn't trust me that far ... he got burned pretty badly a few
years ago by a developer building in back-door access