> I am trying to check a table to see if a value doesnt exist, and I
> would prefer to use the NOT IN syntax, but that doesnt work for mysql.
> I've been trying to construct a similar statement in MySQL, but I'm
> coming up cold.
>
> t1
> 't1id'
> '1'
> '18'
> '19'
>
> t2
> 't2id','t2gid'
> '19','194'
> '1','194'
>
> The problem statement I would like to use is:
>
> SELECT t1id FROM t1 WHERE t1id NOT IN (SELECT t2id FROM t2 WHERE t2gid
> = 194)
>
> Which should only show:
> t1
> 't1id'
> '18'
> and so far the best I can come up with is:
>
> SELECT t1.t1id FROM t1 LEFT JOIN t2 ON t1.t1id=t2.t1id WHERE t2.t1id
> IS NULL OR t2.t2gid IS NULL
You only want one of the IS NULLs - t2.t1id IS NULL. No line will have both
null, but a t1 entry with t2 columns null for every t1 which does not have
a match in t2.
BTW, common practice would give the same column name, id, instead of t2id
and t1id,
if they are meant to represent the same thing, as is usially the case. The
ID is not
the ID in t1 or the ID in t2, it is THE id, which happens to appear in both
tables
in order to do relational joins.
Alec
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
