opposite of sql statement

Forum Moderators: open

Message Too Old, No Replies

opposite of sql statement

defanjos

8:52 pm on Sep 10, 2013 (gmt 0)

Hi,

I have this statement that works fine

SELECT * FROM tblstatus, tblstatus_to_accounts WHERE tblstatus.statid=tblstatus_to_accounts.statid AND tblstatus_to_accounts.aid = '10'

basically pulls all statid's from tblstatus that exist in tblstatus_to_accounts and the aid = 10

But I need the opposite also.
I am trying to get all records that do not equal 10 on tblstatus_to_accounts

I tried so many different things, but can't get it to work.

the tables:

tblstatus
statid | name

tblstatus_to_accounts
id | statid | aid

Thanks in advance

LifeinAsia

9:17 pm on Sep 10, 2013 (gmt 0)

Um, what happened when you tried the following?

SELECT *
FROM tblstatus, tblstatus_to_accounts
WHERE tblstatus.statid=tblstatus_to_accounts.statid AND tblstatus_to_accounts.aid <> '10'

Alternatively, you may need to use != instead of <>.

defanjos

9:52 pm on Sep 10, 2013 (gmt 0)

I tried that at first, but gave me an error because it did not find any records.
I tried both != and <>

error:
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

I also tried statements with "WHERE NOT EXISTS", "NOT IN", "LEFT jOIN", etc..

Could not make it work.

LifeinAsia

10:32 pm on Sep 10, 2013 (gmt 0)

What database are you using? Also, what programming language are you using?

And just for fun, try:
SELECT *
FROM tblstatus INNER JOIN tblstatus_to_accounts ON tblstatus.statid=tblstatus_to_accounts.statid
WHERE tblstatus_to_accounts.aid <> '10'

defanjos

11:01 pm on Sep 10, 2013 (gmt 0)

mySQL with classic ASP

That sql statement did not work either, gave me same error.

LifeinAsia

12:14 am on Sep 11, 2013 (gmt 0)

Strangee...

It's a bit brute force, but maybe this will work:
SELECT *
FROM tblstatus, tblstatus_to_accounts
WHERE tblstatus.statid=tblstatus_to_accounts.statid AND tblstatus.statid
NOT IN (SELECT tblstatus.statid FROM tblstatus, tblstatus_to_accounts WHERE tblstatus.statid=tblstatus_to_accounts.statid AND tblstatus_to_accounts.aid = '10')

defanjos

3:50 am on Sep 11, 2013 (gmt 0)

That did not work either.

I did it differently, so don't need that sql statement any longer. Wasted enough time with it.

LifeinAsia thanks a lot for your help.