Re: [GENERAL] null and =

Lincoln Yeoh wrote:

> At 04:59 PM 05-12-1999 -0500, Mike Mascari wrote:
> >Instead, I would use a correlated subquery with an EXISTS/NOT EXISTS test
> >against the criteria for which you are searching:
> >
> >SELECT t1.a, t1.b FROM dummy t1
> >WHERE EXISTS (SELECT t2.a FROM dummy t2 WHERE t1.a = t2.a)
> >....
> >
>
> I've got two tables, the first table contains "user accounts" and I'd like
> to insert rows in the second table if necessary (if the userids don't exist
> in the second table).
>
> My query to find out which ids need to be inserted is something like this:
> select usr_id,usr_login from users where usr_id not in (select userid from
> wm_accounts);
>
> (first table = users, second table = wm_accounts)
>
> Is there a way of doing this efficiently and effectively? I cannot use
> rules because I need to insert some extra nondatabase data into the second
> table.
>

I would just do:

SELECT users.usr_id, users.usr_login FROM users WHERE NOT EXISTS
(SELECT wm_accounts.usr_id FROM wm_accounts WHERE
wm_accounts.usr_id = users.usr_id);

>
> I would prefer a query which can work if the second table is null. The
> problem with other queries I thought of was they would return null if the
> second table is null.
>

I'm not sure what you mean here. Do you mean the table doesn't exist, is empty,
or that the usr_id field is NULL?

> I could in theory modify the program which inserts rows to the first table
> and make it insert rows to the second table, however I'd like to keep a
> functional and logical separation between the two programs (they aren't
> supposed to be doing each others jobs).
>

What happens if a record is added to wm_accounts? Do you want a record to also
be created in users as well, or is users a superset with respect to wm_accounts
and usr_id?

>
> Any suggestions?
>
> Thanks,
> Link.
>