Help with sql statement - check certain records

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)

Help with sql statement - check certain records

Author

Topic

ramone_johnny
Starting Member

35 Posts

Posted - 2011-07-05 : 23:49:55

Hey guys,
I have the following script below that should be returning results as I know for certain they exist, but for some reason Im not getting any returned?

strSQL = "SELECT mem_ID, mem_type FROM tblmembers WHERE mem_type <> 0 AND mem_ID NOT in ( select share_userID from tblshare_adverts) AND mem_ID NOT in ( select profile_userID from tblprofiles)"

Any ideas why? Or how I can fix this?

Thank you :)

Complete newbie. Please forgive me for having to ask such stupid questions...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-06 : 00:01:00

i don't know how to fix it as we don't know how your data looks like.

But i can tell you how to find the problem yourself.

In your query you have 3 condition in the WHERE clause. Mask out / exclude one or two condition and execute your query and see if you get any result back. Then you add back one condition and execute the query again

KH
[spoiler]Time is always against us[/spoiler]

ramone_johnny
Starting Member

35 Posts

Posted - 2011-07-06 : 00:03:53

Sorry I shouldve given more information.....

tblmembers table holds mem_ID
tblprofiles holds profile_userID - which relates back to mem_ID
tblshare_advert holds share_userID - which also relates back to mem_ID

In 'words', heres what I need to do....

Get the mem_ID from tblmembers table.

Lets check the tblprofiles table to see if they have an advertisement...

Are there any records that match mem_ID with profile_userID ?

No? Okay lets check the tblshare_adverts to see if they have an ad

Are there any records that match mem_ID with share_userID?

No?

Okay then, lets update the mem_type, on the tblmembers table, back to zero because they dont have any ads listed.

Does this make sense?

Complete newbie. Please forgive me for having to ask such stupid questions...

ramone_johnny
Starting Member

35 Posts

Posted - 2011-07-06 : 00:05:49

I just took out ---> AND mem_ID NOT in ( select profile_userID from tblprofiles) and I got results.

But that doesnt really help me much.

Complete newbie. Please forgive me for having to ask such stupid questions...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-06 : 00:11:07

if i understand you correctly, you want an UPDATE query to update the mem_type.

something like this


UPDATE m
SET    mem_type = 0
FROM   tblmembers m
WHERE  mem_type <> 0
AND    NOT EXISTS
       (
           SELECT *
           FROM   tblprofiles x
           WHERE  x.profile_userID = m.mem_ID
       )
AND    NOT EXISTS
       (
           SELECT *
           FROM   tblshare_advert x
           WHERE  x.share_userID   = m.mem_ID
       )

KH
[spoiler]Time is always against us[/spoiler]

ramone_johnny
Starting Member

35 Posts

Posted - 2011-07-06 : 00:12:41

Yes youre right, I DO want to update the mem_type....

But first I want to SEE the results as this query is being run on a live server, and I cannot afford to get this wrong! :(

Complete newbie. Please forgive me for having to ask such stupid questions...

ramone_johnny
Starting Member

35 Posts

Posted - 2011-07-06 : 00:19:59

Okay this looks better....

SELECT mem_type FROM tblmembers m WHERE mem_type <> 0 AND NOT EXISTS ( SELECT * FROM tblprofiles x WHERE x.profile_userID = m.mem_ID ) AND NOT EXISTS ( SELECT * FROM tblshare_adverts x WHERE x.share_userID = m.mem_ID )

Complete newbie. Please forgive me for having to ask such stupid questions...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-06 : 00:26:56

quote:
Originally posted by ramone_johnny

Yes youre right, I DO want to update the mem_type....

But first I want to SEE the results as this query is being run on a live server, and I cannot afford to get this wrong! :(

Complete newbie. Please forgive me for having to ask such stupid questions...

change the "UPDATE m SET mem_type = 0" to a "SELECT * " first. Verify the result and then change to UPDATE when the result is ok

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-06 : 00:31:46

the problem with your original query is you are using NOT IN. If the sub query contain a NULL value, the NOT IN will be false.

Try the following and see the result


declare	@tbl1 table
(
	col	int
)

insert into @tbl1 select 1
insert into @tbl1 select 2
insert into @tbl1 select 3

declare	@tbl2 table
(
	col	int
)

insert into @tbl2 select 2

declare	@tbl3 table
(
	col	int
)

insert into @tbl3 select 3
insert into @tbl3 select NULL

select	*
from	@tbl1 t1
where	col not in (select col from @tbl2)

select	*
from	@tbl1 t1
where	col not in (select col from @tbl3)

KH
[spoiler]Time is always against us[/spoiler]

ramone_johnny
Starting Member

35 Posts

Posted - 2011-07-06 : 01:00:37

Got it!

All sorted, thank you very much for your help :0)

I used your first suggestion above and it worked perfectly - thanks!

UPDATE m
SET mem_type = 0
FROM tblmembers m
WHERE mem_type <> 0
AND NOT EXISTS
(
SELECT *
FROM tblprofiles x
WHERE x.profile_userID = m.mem_ID
)
AND NOT EXISTS
(
SELECT *
FROM tblshare_advert x
WHERE x.share_userID = m.mem_ID
)

Complete newbie. Please forgive me for having to ask such stupid questions...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-06 : 02:29:07

you are welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources