Join Three tables?

[MEM]

hello,

I'm need some help with some sql to return some data...obviousl y ;)

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.

[John Gilson]

"MEM" <mm*****@citlin k.net> wrote in message news:94******** *************** ***@posting.goo gle.com...

hello,

I'm need some help with some sql to return some data...obviousl y ;)

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

[Domenico Discepola]

"MEM" <mm*****@citlin k.net> wrote in message
news:94******** *************** ***@posting.goo gle.com...

hello,

I'm need some help with some sql to return some data...obviousl y ;)

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

[Domenico Discepola]

"MEM" <mm*****@citlin k.net> wrote in message
news:94******** *************** ***@posting.goo gle.com...

hello,

I'm need some help with some sql to return some data...obviousl y ;)

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