need help - select from one table where desn't exists in (select from another table where status=1)

[bpforte]

Hello,
I need help with building query, basically I need to select all records
from one table that don't exists in second table with status 1, but
they can exists in second table with status 0, to be more complicated
there can be in the same record in second table with status 0 and 1
(second table is something like log)

in oracle I can do:
select record from table_1 where record is not in (select record from
table_2 where status=1)

and I'm looking for something similar in mysql

I appreciate any help :)

thx...
Bartek

[www.1-script.com]

bp*****@gmail.com wrote:

Hello,
I need help with building query, basically I need to select all records
from one table that don't exists in second table with status 1, but
they can exists in second table with status 0, to be more complicated
there can be in the same record in second table with status 0 and 1
(second table is something like log)

in oracle I can do:
select record from table_1 where record is not in (select record from
table_2 where status=1)

and I'm looking for something similar in mysql

I appreciate any help :)

thx...
Bartek

If you are lucky enough to have MySQL 4.1 or later you can use NOT EXISTS,
and the syntax is almost exactly what you proposed:

SELECT record AS record_1 FROM table_1 WHERE NOT EXISTS (SELECT record,
status FROM table_2 WHERE status=1 AND record=record_1);

This is assuming 'record' is a single column, not a row.

What is it that you have to do on version 4.0.xx still beats me. I'm
trying to port a script from 5.0 to 4.0.27 for a particular hosting and
almost ready to give up: Error #1064 (syntax error) all the time around
the second SELECT. As if you cannot have the second SELECT which is not
true AFAIK. On top of that EXISTS simply does not exists (pardon the pun)
on the early version.

Good luck!

--
Cheers,
Dmitri
See Site Sig Below

--
+------------------------------------------------+
| Follow mailing.database.mysql threads |
| with your Firefox Live Bookmarks! Set it up at |
| http://www.1-script.com/forums/ |
+------------------------------------------------+