Your sub-select query can be written as:
select t1id from t1 left join t2 on t1id = t2id where t2id is null
in MySQL.
Too restrictive -- the original poster, Phil Dowson, wanted to find all t1 rows except those that have t2.t2gid = 194 (or some other t2gid value).
The above query only generalizes if there is at most one t2 row for each t1 row, and that is not the case here. For a given t1 row, there can be zero, one, or many t2s, possibly one of which would have t2gid=194.
It can be done as either a series of queries, or as a fairly bizarre aggregation:
SELECT t1.t1id FROM t1 LEFT JOIN t2 ON t1.t1id = t2.t2id GROUP BY t1.t1id HAVING min(abs(t2.t2gid - 192)) > 0 OR min(t2.t2gid) IS NULL;
I haven't tested this, but I believe that it will work.
Bruce Feist
The example is actually given in the MySQL manual itself (section 1.7.4.1).
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.
--------------------------------------------------------------------- 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
