Your sub-select query can be written as:
select t1id from t1 left join t2 on t1id = t2id where t2id is null
in MySQL.
The example is actually given in the MySQL manual itself (section 1.7.4.1).
[EMAIL PROTECTED] wrote:
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
-- Gopalarathnam V. Registered GNU/Linux User #300656 http://counter.li.org
--------------------------------------------------------------------- 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
