Finding data that is _not_ on 2 tables?

Hi,

I have 2 tables, TABLE_A and TABLE_B
Both table have a "user_name" section.

I want to run a con job from time to time that would add "user_name" from
TABLE_A if it does not already exist on TABLE_B.
How would I run such an update?

The select would look something like...
Select "user_name" from TABLE_A where "user_name" not in TABLE_B

But "NOT IN" does not seem to be valid, (MySQL 4).

Many thanks.

Simon

"Simon" <spambucket@example.com> wrote in message
news:48l482Fkfi7jU1@individual.net...
> Hi,
>
> I have 2 tables, TABLE_A and TABLE_B
> Both table have a "user_name" section.
>
> I want to run a con job from time to time that would add "user_name" from
> TABLE_A if it does not already exist on TABLE_B.
> How would I run such an update?
>
> The select would look something like...
> Select "user_name" from TABLE_A where "user_name" not in TABLE_B

I'm not sure I understand what you want to do. Do you want to SELECT
entries from table_A that do not occur in table_B? Do you want to INSERT
records to either table? Do you want to UPDATE a record in table_A? By
"user_name," do you mean a literal string, or a column named user_name?

Just going on a guess, here's a query that shows values in the user_name
column of TABLE_A that do not occur in TABLE_B.

SELECT A.user_name
FROM TABLE_A AS A
LEFT OUTER JOIN TABLE_B AS B ON A.user_name = B.user_name
WHERE B.user_name IS NULL

> But "NOT IN" does not seem to be valid, (MySQL 4).

I know what you mean, but for what it's worth, NOT IN is valid in any
version of MySQL.
That is, you can do "NOT IN (1,2,3)" in any version.
However, subqueries are not supported in MySQL 4.0.

Regards,
Bill K.