Home All Groups Group Topic Archive Search About

Query Help, Efficient Exclusion

microsoft.public.sqlserver.programming
Author
24 Mar 2006 6:28 PM
JDP@Work
I need to have in my staff_rank table each staffname and region, but it's OK to
have a duplicate staffNames as long as the region is different.

Please note that, I can't correct the problem of the same staffName having
additional entries with the same region code, there are good reasons this is not
ever going to be fixed.

-- How do I exclude a staffName when the staffName and Region already exist in
my staff_rank table, but allow duplicate staffNames as long as the region is
different?

create table #staff (staffName varchar(40) ,SalesCount int ,Region varchar(1))
create table #Staff_Rank (staffName varchar(40) ,SalesCount int ,Region
varchar(1) ,Rank int)
create table #regions (staffName varchar(40) ,Region varchar(1))

insert #regions select 'Leslie Smith' ,'W'
insert #regions select 'Martin Alme' ,'W'
insert #regions select 'Rosa Minproc' ,'E'
insert #regions select 'Lasiter Morgan' ,'E'
insert #regions select 'Ronnie Hams' ,'W'
insert #regions select 'Lasiter Morgan' ,'W'
insert #regions select 'Martin Alme' ,'W'

insert #staff select 'Leslie Smith' ,21 ,'W'
insert #staff select 'Martin Alme' ,12 ,'W'
insert #staff select 'Rosa Minproc' ,28 ,'E'
insert #staff select 'Lasiter Morgan' ,31 ,'E'
insert #staff select 'Ronnie Hams' ,22 ,'W'
insert #staff select 'Lasiter Morgan' ,9 ,'W'
insert #staff select 'Martin Alme' ,1 ,'W'

insert #staff_rank select s.staffname ,s.salescount ,r.region ,null
from #staff s with(nolock)
join #regions r with(nolock) on s.staffname = r.staffname and s.region =
r.region

select * from #staff_rank order by staffname

drop table #staff
drop  table #Staff_Rank
drop table #regions

Author
24 Mar 2006 8:54 PM
helpful sql
When do  you need to exclude them? Do you have the query that you are trying
to modify?

Show quote
"JDP@Work" <JPGMTNoSpam@sbcglobal.net> wrote in message
news:epde6C3TGHA.4772@TK2MSFTNGP09.phx.gbl...
>I need to have in my staff_rank table each staffname and region, but it's
>OK to
> have a duplicate staffNames as long as the region is different.
>
> Please note that, I can't correct the problem of the same staffName having
> additional entries with the same region code, there are good reasons this
> is not
> ever going to be fixed.
>
> -- How do I exclude a staffName when the staffName and Region already
> exist in
> my staff_rank table, but allow duplicate staffNames as long as the region
> is
> different?
>
> create table #staff (staffName varchar(40) ,SalesCount int ,Region
> varchar(1))
> create table #Staff_Rank (staffName varchar(40) ,SalesCount int ,Region
> varchar(1) ,Rank int)
> create table #regions (staffName varchar(40) ,Region varchar(1))
>
> insert #regions select 'Leslie Smith' ,'W'
> insert #regions select 'Martin Alme' ,'W'
> insert #regions select 'Rosa Minproc' ,'E'
> insert #regions select 'Lasiter Morgan' ,'E'
> insert #regions select 'Ronnie Hams' ,'W'
> insert #regions select 'Lasiter Morgan' ,'W'
> insert #regions select 'Martin Alme' ,'W'
>
> insert #staff select 'Leslie Smith' ,21 ,'W'
> insert #staff select 'Martin Alme' ,12 ,'W'
> insert #staff select 'Rosa Minproc' ,28 ,'E'
> insert #staff select 'Lasiter Morgan' ,31 ,'E'
> insert #staff select 'Ronnie Hams' ,22 ,'W'
> insert #staff select 'Lasiter Morgan' ,9 ,'W'
> insert #staff select 'Martin Alme' ,1 ,'W'
>
> insert #staff_rank select s.staffname ,s.salescount ,r.region ,null
> from #staff s with(nolock)
> join #regions r with(nolock) on s.staffname = r.staffname and s.region =
> r.region
>
> select * from #staff_rank order by staffname
>
> drop table #staff
> drop  table #Staff_Rank
> drop table #regions
>
>
Author
25 Mar 2006 12:48 AM
oj
insert #staff_rank
select distinct s.staffname ,s.salescount ,r.region ,null
from #staff s with(nolock)
join #regions r with(nolock) on s.staffname = r.staffname and s.region =
r.region

--
-oj


Show quote
"JDP@Work" <JPGMTNoSpam@sbcglobal.net> wrote in message
news:epde6C3TGHA.4772@TK2MSFTNGP09.phx.gbl...
>I need to have in my staff_rank table each staffname and region, but it's
>OK to
> have a duplicate staffNames as long as the region is different.
>
> Please note that, I can't correct the problem of the same staffName having
> additional entries with the same region code, there are good reasons this
> is not
> ever going to be fixed.
>
> -- How do I exclude a staffName when the staffName and Region already
> exist in
> my staff_rank table, but allow duplicate staffNames as long as the region
> is
> different?
>
> create table #staff (staffName varchar(40) ,SalesCount int ,Region
> varchar(1))
> create table #Staff_Rank (staffName varchar(40) ,SalesCount int ,Region
> varchar(1) ,Rank int)
> create table #regions (staffName varchar(40) ,Region varchar(1))
>
> insert #regions select 'Leslie Smith' ,'W'
> insert #regions select 'Martin Alme' ,'W'
> insert #regions select 'Rosa Minproc' ,'E'
> insert #regions select 'Lasiter Morgan' ,'E'
> insert #regions select 'Ronnie Hams' ,'W'
> insert #regions select 'Lasiter Morgan' ,'W'
> insert #regions select 'Martin Alme' ,'W'
>
> insert #staff select 'Leslie Smith' ,21 ,'W'
> insert #staff select 'Martin Alme' ,12 ,'W'
> insert #staff select 'Rosa Minproc' ,28 ,'E'
> insert #staff select 'Lasiter Morgan' ,31 ,'E'
> insert #staff select 'Ronnie Hams' ,22 ,'W'
> insert #staff select 'Lasiter Morgan' ,9 ,'W'
> insert #staff select 'Martin Alme' ,1 ,'W'
>
> insert #staff_rank select s.staffname ,s.salescount ,r.region ,null
> from #staff s with(nolock)
> join #regions r with(nolock) on s.staffname = r.staffname and s.region =
> r.region
>
> select * from #staff_rank order by staffname
>
> drop table #staff
> drop  table #Staff_Rank
> drop table #regions
>
>
Author
25 Mar 2006 5:04 AM
JDP@Work
Oh, Jaaa

Thank you....

I knew that I would have made it more complicated, using where not in(selects...

JeffP....

Show quote
"oj" <nospam_ojngo@home.com> wrote in message
news:OOFpeX6TGHA.4492@TK2MSFTNGP09.phx.gbl...
> insert #staff_rank
> select distinct s.staffname ,s.salescount ,r.region ,null
> from #staff s with(nolock)
> join #regions r with(nolock) on s.staffname = r.staffname and s.region =
> r.region
>
> --
> -oj
>
>
> "JDP@Work" <JPGMTNoSpam@sbcglobal.net> wrote in message
> news:epde6C3TGHA.4772@TK2MSFTNGP09.phx.gbl...
> >I need to have in my staff_rank table each staffname and region, but it's
> >OK to
> > have a duplicate staffNames as long as the region is different.
> >
> > Please note that, I can't correct the problem of the same staffName having
> > additional entries with the same region code, there are good reasons this
> > is not
> > ever going to be fixed.
> >
> > -- How do I exclude a staffName when the staffName and Region already
> > exist in
> > my staff_rank table, but allow duplicate staffNames as long as the region
> > is
> > different?
> >
> > create table #staff (staffName varchar(40) ,SalesCount int ,Region
> > varchar(1))
> > create table #Staff_Rank (staffName varchar(40) ,SalesCount int ,Region
> > varchar(1) ,Rank int)
> > create table #regions (staffName varchar(40) ,Region varchar(1))
> >
> > insert #regions select 'Leslie Smith' ,'W'
> > insert #regions select 'Martin Alme' ,'W'
> > insert #regions select 'Rosa Minproc' ,'E'
> > insert #regions select 'Lasiter Morgan' ,'E'
> > insert #regions select 'Ronnie Hams' ,'W'
> > insert #regions select 'Lasiter Morgan' ,'W'
> > insert #regions select 'Martin Alme' ,'W'
> >
> > insert #staff select 'Leslie Smith' ,21 ,'W'
> > insert #staff select 'Martin Alme' ,12 ,'W'
> > insert #staff select 'Rosa Minproc' ,28 ,'E'
> > insert #staff select 'Lasiter Morgan' ,31 ,'E'
> > insert #staff select 'Ronnie Hams' ,22 ,'W'
> > insert #staff select 'Lasiter Morgan' ,9 ,'W'
> > insert #staff select 'Martin Alme' ,1 ,'W'
> >
> > insert #staff_rank select s.staffname ,s.salescount ,r.region ,null
> > from #staff s with(nolock)
> > join #regions r with(nolock) on s.staffname = r.staffname and s.region =
> > r.region
> >
> > select * from #staff_rank order by staffname
> >
> > drop table #staff
> > drop  table #Staff_Rank
> > drop table #regions
> >
> >
>
>
Author
25 Mar 2006 6:07 AM
oj
ya welcome. ;-)

--
-oj



Show quote
"JDP@Work" <JPGMTNoSpam@sbcglobal.net> wrote in message
news:ePfr8n8TGHA.4276@TK2MSFTNGP10.phx.gbl...
> Oh, Jaaa
>
> Thank you....
>
> I knew that I would have made it more complicated, using where not
> in(selects...
>
> JeffP....
>
> "oj" <nospam_ojngo@home.com> wrote in message
> news:OOFpeX6TGHA.4492@TK2MSFTNGP09.phx.gbl...
>> insert #staff_rank
>> select distinct s.staffname ,s.salescount ,r.region ,null
>> from #staff s with(nolock)
>> join #regions r with(nolock) on s.staffname = r.staffname and s.region =
>> r.region
>>
>> --
>> -oj
>>
>>
>> "JDP@Work" <JPGMTNoSpam@sbcglobal.net> wrote in message
>> news:epde6C3TGHA.4772@TK2MSFTNGP09.phx.gbl...
>> >I need to have in my staff_rank table each staffname and region, but
>> >it's
>> >OK to
>> > have a duplicate staffNames as long as the region is different.
>> >
>> > Please note that, I can't correct the problem of the same staffName
>> > having
>> > additional entries with the same region code, there are good reasons
>> > this
>> > is not
>> > ever going to be fixed.
>> >
>> > -- How do I exclude a staffName when the staffName and Region already
>> > exist in
>> > my staff_rank table, but allow duplicate staffNames as long as the
>> > region
>> > is
>> > different?
>> >
>> > create table #staff (staffName varchar(40) ,SalesCount int ,Region
>> > varchar(1))
>> > create table #Staff_Rank (staffName varchar(40) ,SalesCount int ,Region
>> > varchar(1) ,Rank int)
>> > create table #regions (staffName varchar(40) ,Region varchar(1))
>> >
>> > insert #regions select 'Leslie Smith' ,'W'
>> > insert #regions select 'Martin Alme' ,'W'
>> > insert #regions select 'Rosa Minproc' ,'E'
>> > insert #regions select 'Lasiter Morgan' ,'E'
>> > insert #regions select 'Ronnie Hams' ,'W'
>> > insert #regions select 'Lasiter Morgan' ,'W'
>> > insert #regions select 'Martin Alme' ,'W'
>> >
>> > insert #staff select 'Leslie Smith' ,21 ,'W'
>> > insert #staff select 'Martin Alme' ,12 ,'W'
>> > insert #staff select 'Rosa Minproc' ,28 ,'E'
>> > insert #staff select 'Lasiter Morgan' ,31 ,'E'
>> > insert #staff select 'Ronnie Hams' ,22 ,'W'
>> > insert #staff select 'Lasiter Morgan' ,9 ,'W'
>> > insert #staff select 'Martin Alme' ,1 ,'W'
>> >
>> > insert #staff_rank select s.staffname ,s.salescount ,r.region ,null
>> > from #staff s with(nolock)
>> > join #regions r with(nolock) on s.staffname = r.staffname and s.region
>> > =
>> > r.region
>> >
>> > select * from #staff_rank order by staffname
>> >
>> > drop table #staff
>> > drop  table #Staff_Rank
>> > drop table #regions
>> >
>> >
>>
>>
>
>

AddThis Social Bookmark Button

Post Other interesting topics
Dynamic IDENTITY seed
Deadlock help on single table
Cascading stock values?
Greater than zero
smalldatetime or integer