homepage Welcome to WebmasterWorld Guest from 54.227.11.45
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
opposite of sql statement
defanjos




msg:4608801
 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




msg:4608809
 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




msg:4608815
 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




msg:4608820
 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




msg:4608836
 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




msg:4608845
 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




msg:4608873
 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved