hekp with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

hekp with query

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-11-07 : 14:33:21

i have a table like this :
account,bankid,branchid,status
for example :

quote:

account,bankid,branchid,status
123 2 3 5
123 2 3 3
123 2 3 1

the all 3 keys : account,bankid,branchid can repeat on themselfs with diffrent status
i want to get a list with all the account,bankid,branchid that dosent contians a certain status at all!
for example if i dont want status 5 then in the above case i will get false,but if i wantto check the the above didnt have status 22 i will get TRUE!
how do i do this?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 14:39:50

You don't make sense with your requirement.

Either

select  1 - sign(count(*))
from    mytable
where   status = 22

It will return 1 if status is not found or 0 if found

or

select distinct account, bankid, status from mytable where status <> 22

Peter Larsson
Helsingborg, Sweden

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-11-07 : 14:43:40

that wasnt what i meant
if in my example there is a row with status 5 for a certain
account+bankid+branchid and i dont want that!!!
then your query isnt good beacuse in that case i dont want to get the certain account,bankid,branchid at all!
all the idea that if a certain status does exist and i dont want to be in my account+bankid+branchid but its there then i dont want get the account+bankid+branchid at all!

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 14:47:14

If you learn to write clearer questions, maybe you will get your answer faster?

select distinct	mt.*
from		mytable mt
left join	(
			select	account,
				bankid,
				branchid
			from	mytable
			where	status = 5
		) q ON q.acocunt = mt.account and q.bankid = mt.bankid and q.branchid = mt.branchid
where		q.account is null

Peter Larsson
Helsingborg, Sweden

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-11-07 : 15:12:28

your correct PESO sorry for the unclear
thnaks for the answer!

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 15:17:30

In my query above, the first column name after ON should be q.account, and nothing else...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources