Converting to ANSI joins

Is it possible to convert the following query to use ANSI join syntax?
It may be already but I'm suspecting the "WHERE t2.table_id =
t1.table_id" means it isn't. I tried things like the bottom query but
the number of records returned doesn't match. Any help would be
appreciated. I can't give the DDL or test date cause this is a
simplification of the actual query. Thanks a lot!
SELECT DISTINCT t1.table_id
FROM mytable t1
WHERE (t1.username = 'john')
AND (NOT EXISTS (SELECT * FROM mytable t2 WHERE t2.table_id =
t1.table_id AND t2.username <> 'john'))
-- my attempt --
SELECT DISTINCT t1.table_id
FROM mytable t1
INNER JOIN mytable t2 ON t2.table_id = t1.table_id AND t2.username =
'john'
WHERE (t1.username = 'john')It's not a JOIN but a correlated subquery. It is possible to replace this
clause with a LEFT JOIN and testing for the presence of the null value at
the right side; something like:
SELECT DISTINCT t1.table_id
FROM mytable t1 LEFT JOIN mytable t2 on (t2.table_id = t1.table_id AND
t2.username <> 'john')
WHERE (t1.username = 'john')
And t2.table_id is Null
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"phils" <jlee@.transfuture.net> wrote in message
news:1149545664.941792.259700@.h76g2000cwa.googlegroups.com...
> Is it possible to convert the following query to use ANSI join syntax?
> It may be already but I'm suspecting the "WHERE t2.table_id =
> t1.table_id" means it isn't. I tried things like the bottom query but
> the number of records returned doesn't match. Any help would be
> appreciated. I can't give the DDL or test date cause this is a
> simplification of the actual query. Thanks a lot!
> SELECT DISTINCT t1.table_id
> FROM mytable t1
> WHERE (t1.username = 'john')
> AND (NOT EXISTS (SELECT * FROM mytable t2 WHERE t2.table_id =
> t1.table_id AND t2.username <> 'john'))
>
> -- my attempt --
> SELECT DISTINCT t1.table_id
> FROM mytable t1
> INNER JOIN mytable t2 ON t2.table_id = t1.table_id AND t2.username =
> 'john'
> WHERE (t1.username = 'john')
>|||Thank you Sylvain I will try that.
Phil|||It works! Thank you Sylvain !!!|||The advantage of the LEFT JOIN is that on many occasions it will be faster
then the use of an Exists statement (not sure if it's still true but it was
some years ago).
However, it's much more easier to write some complex filterings with an
Exists() function than with the LEFT JOIN.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"phils" <jlee@.transfuture.net> wrote in message
news:1149603901.621194.200240@.h76g2000cwa.googlegroups.com...
> It works! Thank you Sylvain !!!
>