467,879 Members | 1,316 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,879 developers. It's quick & easy.

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

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

Sep 21 '06 #1
  • viewed: 5551
Share:
1 Reply

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/ |
+------------------------------------------------+

Sep 28 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Chris Foster | last post: by
9 posts views Thread by netpurpose | last post: by
3 posts views Thread by Ian T | last post: by
1 post views Thread by mlrehberg | last post: by
4 posts views Thread by jimh | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.