MS SQL Server - stored procedure head scratching problem

Author

stored procedure head scratching problem

mark

2005-09-05, 11:28 am

i have two tables structured like this (for example) :-

table 1 (personal)

ID,firstname,surname
,telephone
(id is an identity field)

table 2(notes)

ID,personal_id,note,
valid
(id is an identity field)

the data can be entered like such in the notes table (its been made like
that to prevent duplicates)
valid indicates wether the note is still valid (true or false)

1,34,test note,false
2,34,test note 2,false
3,34,test note 3,true

problem stems creating a stored procedure

i want to select id,surname,note,vali
d where valid=false, (BUT EXCLUDE
SURNAME IF THEY HAVE A VALID=TRUE)
however this still selects the surname where valid = true
and duplicates eg the result set would be

34,smith,test note,false
34,smith,test note 2,false

however i dont want to return these rows as the user has a valid note entry

how can i do this ? - hope ive explained it properly - my brain hurts!

mark

Ed Enstrom

2005-09-05, 8:23 pm

mark wrote:
> i have two tables structured like this (for example) :-
>
> table 1 (personal)
>
> ID,firstname,surname
,telephone
> (id is an identity field)
>
> table 2(notes)
>
> ID,personal_id,note,
valid
> (id is an identity field)
>
> the data can be entered like such in the notes table (its been made like
> that to prevent duplicates)
> valid indicates wether the note is still valid (true or false)
>
> 1,34,test note,false
> 2,34,test note 2,false
> 3,34,test note 3,true
>
> problem stems creating a stored procedure
>
> i want to select id,surname,note,vali
d where valid=false, (BUT EXCLUDE
> SURNAME IF THEY HAVE A VALID=TRUE)
> however this still selects the surname where valid = true
> and duplicates eg the result set would be
>
> 34,smith,test note,false
> 34,smith,test note 2,false
>
> however i dont want to return these rows as the user has a valid note entry
>
> how can i do this ? - hope ive explained it properly - my brain hurts!
>
> mark

I'm not sure I understand what you are asking for, but it sounds like a subquery
would do it.

select ...
from ...
where valid = false
and id not in (select id from notes where valid = true)

mark

2005-09-06, 3:23 am

"Ed Enstrom" <nospam@invalid.net> wrote in message
news:yS3Te.10426$OT1.9186@fe09.lga...
> mark wrote:
>
> I'm not sure I understand what you are asking for, but it sounds like a
> subquery would do it.
>
> select ...
> from ...
> where valid = false
> and id not in (select id from notes where valid = true)
>

ill look into that, basically what i was trying to do was exclude any IDs
that have a valid note entry

eg

1,32,smith,note1,tru
e
1,32,smith,note2,fal
se

1,33,jones,note,fals
e
1,33,jones,note,fals
e

32 should be excluded from the query as it has a valid=true

33 should be included as its got no valid=true

cheers

mark

mark

2005-09-06, 9:23 am

i tried the sub query and it seems to be working!
thanks for putting me on the right track, really appreciated

cheers

mark

Sponsored Links