SEO

#1 (**permalink**) 02-28-2008, 06:32 PM

hello,

I'm need some help with some sql to return some data...obviously

I have 3 tables. tblMgr, tblSup, and tblRep. I'd like to get all the
names of the reps that are assigned to a particular manager.

Here is a simplified exmaple of table setup:

tblRep
-------
repid repname supid
----------------------
1 joe 500
2 ann 501

tblSup
-------
supid supname mgrid
----------------------
500 bill 1000
502 tom 1001

tblMgr
-------
mgrid mgrname
---------------
1000 andy
1001 roger

Thanks.

#2 (**permalink**) 02-28-2008, 06:32 PM

"MEM" <mmaxsom@citlink.net> wrote in message news:9403d191.0310271531.75aa7bf4@posting.google.c om...
> hello,
>
> I'm need some help with some sql to return some data...obviously

>
> I have 3 tables. tblMgr, tblSup, and tblRep. I'd like to get all the
> names of the reps that are assigned to a particular manager.
>
> Here is a simplified exmaple of table setup:
>
> tblRep
> -------
> repid repname supid
> ----------------------
> 1 joe 500
> 2 ann 501
>
>
> tblSup
> -------
> supid supname mgrid
> ----------------------
> 500 bill 1000
> 502 tom 1001
>
>
> tblMgr
> -------
> mgrid mgrname
> ---------------
> 1000 andy
> 1001 roger
>
> Thanks.

CREATE VIEW RepsManagers (repid, repname, mgrid, mgrname)
AS
SELECT R.repid, R.repname, M.mgrid, M.mgrname
FROM tblRep AS R
INNER JOIN
tblSup AS S
ON R.supid = S.supid
INNER JOIN
tblMgr AS M
ON S.mgrid = M.mgrid

-- For example
SELECT *
FROM RepsManagers
WHERE mgrname = 'andy'

Regards,
jag

#3 (**permalink**) 02-28-2008, 06:33 PM

"MEM" <mmaxsom@citlink.net> wrote in message
news:9403d191.0310271531.75aa7bf4@posting.google.c om...
> hello,
>
> I'm need some help with some sql to return some data...obviously

>
> I have 3 tables. tblMgr, tblSup, and tblRep. I'd like to get all the
> names of the reps that are assigned to a particular manager.
>

You didn't specify if you wanted all managers (even if they don't have any
reps assigned)... If this is the case, you'll need to use an outer join...
This script creates tables in tempdb... Hope this helps...

use tempdb
go
drop table tblRep
drop table tblSup
drop table tblMgr

go
create table tblRep (repid int, repname char(5), supid int)
create table tblSup (supid int, supname char(5), mgrid int)
create table tblMgr (mgrid int, mgrname char(5))
go

insert into tblRep values (1,'name1', 1)
insert into tblRep values (2,'name2', 1)

insert into tblSup values (1,'sup1',10)
insert into tblSup values (2,'sup2',10)
insert into tblSup values (4,'sup4',20)

insert into tblMgr values (1, 'mgr1')
insert into tblMgr values (10, 'mgr10')
insert into tblMgr values (20, 'mgr20')
go

select
m.mgrid, m.mgrname, r.repid, r.repname
from tblMgr m
left outer join tblSup s on s.mgrid = m.mgrid
left outer join tblRep r on r.supid = s.supid
go

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Join Three tables?