May 2, 2012 at 5:14 pm
hello friends
i have task
teacheridtlnametfnameschoolidschoolname
2 john smith 152 carnegi
2 john smith 155 monroville
1 ricky johnston153 elementery
3 ricky johnston157 alleghany
1 steve ulman 151 banksville
1 steve ulman 154 school
1 steve ulman 156 pittsburgh
i get this result by this query
select t.teacherid,t.tlname,t.tfname, t.schoolid, s.schoolname from teacher t join school s on
t.schoolid = s.schoolid order by t.tlname,t.tfname
and i need result display like this
teacherid tlnametfnameschoolidsecondary_schoolID schoolname
2 john smith 152 carnegi
2 john smith 155 monroville
1 ricky johnston153 elementery
3 ricky johnston 157 alleghany
1 steve ulman 151 banksville
1 steve ulman 154 school
1 steve ulman 156 pittsburgh
here is a data
create table teacher (teacherid int, tlname varchar(12), tfname varchar(12), schoolid int)
insert into teacher values (1, 'steve','ulman',151)
insert into teacher values (2, 'john','smith',152)
insert into teacher values (1, 'ricky','johnston',153)
insert into teacher values (1, 'steve','ulman',154)
insert into teacher values (2, 'john','smith',155)
insert into teacher values (1, 'steve','ulman',156)
insert into teacher values (3, 'ricky','johnston',157)
select * from teacher
create table school (schoolid int, schoolname varchar(12))
insert into school values (151, 'banksville')
insert into school values (152, 'carnegi')
insert into school values (153, 'elementery')
insert into school values (154, 'school')
insert into school values (155, 'monroville')
insert into school values (156, 'pittsburgh')
insert into school values (157, 'alleghany')
so please help me
May 2, 2012 at 6:24 pm
Here's a solution (though not the nicest thing to look at). A few notes, your "teacherid" field doesn't seem like much of an "id". Unless I'm completely missing something, it isn't unique for any teacher. As for the below (extremely long and nested) query, hopefully somebody has a more elegant solution:
select
c.tlname,
c.tfname,
c.schoolid,
c.secondary_schoolID,
s.schoolname
from
(
select
*
from
(
select
tlname,
tfname,
schoolid,
null as secondary_schoolID,
row_number() over(partition by tlname, tfname order by schoolid) as rownum
from teacher
) a
where rownum = 1
union all
select *
from
(
select
tlname,
tfname,
null as schoolid,
schoolid as secondary_schoolID,
row_number() over(partition by tlname, tfname order by schoolid) as rownum
from teacher
) b
where rownum = 2
) c
left join school s
on c.schoolid = s.schoolid
or c.secondary_schoolID = s.schoolid
May 2, 2012 at 6:28 pm
We dont need , 2 ROW_NUMBERs
this will do
; with cte as
(
select t.teacherid,t.tlname,t.tfname, cast(t.schoolid as varchar) as schoolid, s.schoolname
, rn = row_number() over (partition by t.tlname,t.tfname order by s.schoolid)
from teacher t
join school s on
t.schoolid = s.schoolid
)
select c.teacherid , c.tfname , c.tlname
,schoolid = case when rn = 1 then c.schoolid else '' end
,secondary_schoolID = case when rn <> 1 then c.schoolid else '' end
,c.schoolname
from cte c
But surma.sql, why are u creatin the same thread again and again in all the forums?
BE PATIENT. People will help you. If you cant wait, then hire a professional.
May 2, 2012 at 6:29 pm
Thomas Stringer (5/2/2012)
A few notes, your "teacherid" field doesn't seem like much of an "id".
Completely agree.. it looks more of a dud column..
May 2, 2012 at 7:31 pm
ColdCoffee (5/2/2012)
But surma.sql, why are u creatin the same thread again and again in all the forums?
Heh... that's easy. It's "urgent". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply