SQL statement on inner join that hits another table twice?

[dmonroe]

hi group --

Im having a nested inner join problem with an Access SQl
statement/Query design. Im running the query from ASP and not usng the
access interface at all. Here's the tables:

tblEmployees
empId -- EmpName -- EmpRole -- EmpManager
-------....------------.... ---------....---------------
1........ dan yella.......... 1..........2
2.........joe brown........2. ..........5
3.........steve green......3... ........4
4.........tom terrific....... .4..........5
4.........nacho libre........5. .........5
tblRoles
RoleId -- RoleName
--------... -------------
1.........Penci l Jockey
2.........Monke y Manager
3.........Clien t Harrasser
4.........Teleh arassment Manager
5.........Big Cheese
tblSchedule
SchedId -- EmpId -- Days
----------...-------....------
1.............1 .........M-F
2.............2 .........F,S,Su
I would like run a basic inner join query that will result in the
following result sets displayed:

Emp Name ---- EmpRole ----- EmpSchd ---- EmpBoss ----- EmpBossRole
------------...-----------------..-------------...--------------...---------------
Dan Yella....Pencil Jockey....M-F........Joe Brown......Monk ey Manager
Joe Brown..Monkey Manager...F,S,S u...Nacho libre......Big Cheese
Stv Green..Client Harrasser..M-F,Su...Tom Terrific......T ele Manager

Im having trouble with the inner join that has to hit the Employees
table twice to get both the 'emp Name' and then the emp's 'manager's
name'. I have no idea how to do that and have tried about 15
different flavors of SQL inner join syntaxes, and still no luck .

Anyone have a suggestion, or even better, a pointer to a site with an
example? I know Im gonna kick myself when I see it, it's gotta be
something simple Im overlooking.

Thanks,
Dave

[Bas Cost Budde]

You don't say what you have tried. Include your best guess!
--
Bas Cost Budde
Holland

[Rich P]

Try your query inside an Access mdb. If the query doesn't work inside
the Access mdb then you can find out what is wrong locally. That is
much easier than trying to debug sql from an .asp file. If the query
does work correctly inside the Access mdb - then the problem is not with
the query - it is with the .asp file - usually missing a tag <% %>.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Dave M]

On Tue, 19 Sep 2006 13:26:23 GMT,
dm*****@mungedR emovethisAmeric anways.com wrote:

>>hi group --

Im having a nested inner join problem with an Access SQl
statement/Query design. Im running the query from ASP and not usng the
access interface at all. Here's the tables:

tblEmployee s
empId -- EmpName -- EmpRole -- EmpManager
-------....------------.... ---------....---------------
1........ dan yella.......... 1..........2
2.........j oe brown........2. ..........5
3.........ste ve green......3... ........4
4.........t om terrific....... .4..........5
4.........nac ho libre........5. .........5
tblRoles
RoleId -- RoleName
--------... -------------
1.........Pen cil Jockey
2.........Mon key Manager
3.........Cli ent Harrasser
4.........Tel eharassment Manager
5.........B ig Cheese
tblSchedule
SchedId -- EmpId -- Days
----------...-------....------
1............ .1.........M-F
2............ .2.........F,S, Su
I would like run a basic inner join query that will result in the
following result sets displayed:

Emp Name ---- EmpRole ----- EmpSchd ---- EmpBoss ----- EmpBossRole
------------...-----------------..-------------...--------------...---------------
Dan Yella....Pencil Jockey....M-F........Joe Brown......Monk ey Manager
Joe Brown..Monkey Manager...F,S,S u...Nacho libre......Big Cheese
Stv Green..Client Harrasser..M-F,Su...Tom Terrific......T ele Manager

Im having trouble with the inner join that has to hit the Employees
table twice to get both the 'emp Name' and then the emp's 'manager's
name'. I have no idea how to do that and have tried about 15
different flavors of SQL inner join syntaxes, and still no luck .

Anyone have a suggestion, or even better, a pointer to a site with an
example? I know Im gonna kick myself when I see it, it's gotta be
something simple Im overlooking.

Thanks,
Dave

>On Tue, 19 Sep 2006 16:00:19 +0200,
Bas Cost Budde <b.*********@de v.null.comwrote :

>You don't say what you have tried. Include your best guess!

Here's my base query that Ive been working off of, but it doesn't
lookup up the details, in the Role or Emp tables it just returns
integers for the columns:

SELECT emp.empName as EmpName,
emp.EmpRole as EmpRole,
sched.Days as EmpSched,
emp.EmpManager as EmpBoss,
role.empRole as EmpBossRole
FROM tblSchedule as Sched
INNER JOIN (tblemployees as emp
ON sched.empid = emp.empid)
INNER JOIN (tblRoles as role
ON emp.role = role.roleid);
Off that base statement, Ive plugged in and floated 20 -30 variations
at this point. I didnt mention in my OP, but Im trying to avoid
subqueries by using the INNER JOIN syntax that might slow down the
statements execution. Im just not that familiar with explictly
writing inner joins, much less Access 2000 syntax, to boot.

Thanks

[Bas Cost Budde]

To get an employee manager:

SELECT emp.empName, boss.empname AS empBoss
FROM tblemployees AS boss INNER JOIN tblemployees AS emp
ON boss.empid=emp. empmanager;

In your query,

SELECT emp.empName as EmpName,
emp.EmpRole as EmpRole,
sched.Days as EmpSched,
emp.EmpManager as EmpBoss,
role.empRole as EmpBossRole
FROM tblSchedule as Sched
INNER JOIN (tblemployees as emp
ON sched.empid = emp.empid)
INNER JOIN (tblRoles as role
ON emp.role = role.roleid);

you SELECT the emprole fields. Select names instead and you should have
the result you sketch. My statement would be this:

SELECT emp.empName AS EmpName, role.RoleName AS EmpRole, sched.Days AS
EmpSchd, boss.empName AS EmpBoss, bossrole.RoleNa me AS EmpBossRole
FROM (((tblEmployee AS emp INNER JOIN tblSchedule AS sched ON
sched.empid=emp .empid) INNER JOIN tblEmployee AS boss ON
emp.empmanager= boss.empid) INNER JOIN tblRoles AS role ON
emp.empRole=rol e.roleID) INNER JOIN tblRoles as bossrole ON
boss.empRole=bo ssrole.roleID

Here's my base query that Ive been working off of, but it doesn't
lookup up the details, in the Role or Emp tables it just returns
integers for the columns:

SELECT emp.empName as EmpName,
emp.EmpRole as EmpRole,
sched.Days as EmpSched,
emp.EmpManager as EmpBoss,
role.empRole as EmpBossRole
FROM tblSchedule as Sched
INNER JOIN (tblemployees as emp
ON sched.empid = emp.empid)
INNER JOIN (tblRoles as role
ON emp.role = role.roleid);
Off that base statement, Ive plugged in and floated 20 -30 variations
at this point. I didnt mention in my OP, but Im trying to avoid
subqueries by using the INNER JOIN syntax that might slow down the
statements execution. Im just not that familiar with explictly
writing inner joins, much less Access 2000 syntax, to boot.

Thanks

--
Bas Cost Budde
Holland

[Dave M]

>Here's my base query that Ive been working off of, but it doesn't
lookup up the details, in the Role or Emp tables it just returns
integers for the columns:

SELECT emp.empName as EmpName,
emp.EmpRole as EmpRole,
sched.Days as EmpSched,
emp.EmpManager as EmpBoss,
role.empRole as EmpBossRole
FROM tblSchedule as Sched
INNER JOIN (tblemployees as emp
ON sched.empid = emp.empid)
INNER JOIN (tblRoles as role
ON emp.role = role.roleid);
Off that base statement, Ive plugged in and floated 20 -30 variations
at this point. I didnt mention in my OP, but Im trying to avoid
subqueries by using the INNER JOIN syntax that might slow down the
statements execution. Im just not that familiar with explictly
writing inner joins, much less Access 2000 syntax, to boot.

Thanks
Dave

On Tue, 19 Sep 2006 19:38:08 +0200, Bas Cost Budde
<b.*********@de v.null.comwrote :

>To get an employee manager:

SELECT emp.empName, boss.empname AS empBoss
FROM tblemployees AS boss INNER JOIN tblemployees AS emp
ON boss.empid=emp. empmanager;

In your query,

SELECT emp.empName as EmpName,
emp.EmpRole as EmpRole,
sched.Days as EmpSched,
emp.EmpManager as EmpBoss,
role.empRole as EmpBossRole
FROM tblSchedule as Sched
INNER JOIN (tblemployees as emp
ON sched.empid = emp.empid)
INNER JOIN (tblRoles as role
ON emp.role = role.roleid);

you SELECT the emprole fields. Select names instead and you should have
the result you sketch. My statement would be this:

SELECT emp.empName AS EmpName, role.RoleName AS EmpRole, sched.Days AS
EmpSchd, boss.empName AS EmpBoss, bossrole.RoleNa me AS EmpBossRole
FROM (((tblEmployee AS emp INNER JOIN tblSchedule AS sched ON
sched.empid=em p.empid) INNER JOIN tblEmployee AS boss ON
emp.empmanager =boss.empid) INNER JOIN tblRoles AS role ON
emp.empRole=ro le.roleID) INNER JOIN tblRoles as bossrole ON
boss.empRole=b ossrole.roleID

ok, thanks much, Bas. That's exactly the column data I was trying to
return-- but even better, I think I now grasp the pattern needed to
construct those type of joins in the future. The key point i was
missing is that there needs to be a separate inner join to all 'lookup
tables' for each item that needs it. Just because youre hitting the
'role' table for the a record to get the emp's role info on one join,
you still have to create yet another join to get the emp's manager's
role too--- even though they are both part of the same record. Got
it.

------------------------------------
Four quick follow up questions so I can digest and fully grok what
you've shown.

1.The query as is, pulls back just one row from the emp table. No
biggie. Im sure I can hunt down why, obviously one of the joins is
acting as a limiting filter. But I was expecting the query to return
one row for every employee in the emp table, since there is no 'where
clause' to filter the data. Just wondering it that's normal among
nested inner joins?

2. Do you personally construct the joins from the inner joins out, or
the outter joins in? Does Access care about the order? ( Ill
experiment a bit and see myself, just wondering how others with
experience do it?)

2.Performancewi se, if you know, does the jet engine take a speed hit
for each additional 'inner join' a query has? IOW, is 6 'inner joins"
is 'x' amont slower than 3 inner joins?

3. Does the order of the nested 'inner joins' make a difference to
performance? ( again, Ill play with it later, just curious for a quick
answer)

Thanks again for setting me on the correct path.

Dave

[Bas Cost Budde]

ok, thanks much, Bas. That's exactly the column data I was trying to

return-- but even better, I think I now grasp the pattern needed to
construct those type of joins in the future. The key point i was
missing is that there needs to be a separate inner join to all 'lookup
tables' for each item that needs it. Just because youre hitting the
'role' table for the a record to get the emp's role info on one join,
you still have to create yet another join to get the emp's manager's
role too--- even though they are both part of the same record. Got
it.

Good! And that for a forgotten "AIR CODE" warning :) Let's get back to
that in a second.

------------------------------------
Four quick follow up questions so I can digest and fully grok what
you've shown.

1.The query as is, pulls back just one row from the emp table. No
biggie. Im sure I can hunt down why, obviously one of the joins is
acting as a limiting filter. But I was expecting the query to return
one row for every employee in the emp table, since there is no 'where
clause' to filter the data. Just wondering it that's normal among
nested inner joins?

Inner join, functionally seen, is not too much different from WHERE. So,
yes, inner joins normally limit the number of rows.

But here I think I have been over-limiting. Insert parentheses somewhere
:D no, seriously, call me back if you can't catch that one, I'll set up
a small test environment and come up with the correct statement then.
Only giving you pointers here.

Does every employee have a schedule? Does every role occur? with every
manager as well? If not, you must use left joins here (and step
carefully to avoid the sequence mines)

2. Do you personally construct the joins from the inner joins out, or
the outter joins in?

Neither. I write whichever seems "closely related" next to each other,
and build on from these.
To be honest: I always do it from the interface. Which answers:

Does Access care about the order? ( Ill
experiment a bit and see myself, just wondering how others with
experience do it?)

Certainly, that is: I certainly think so.

Now, let's renumber your questions :D

3.Performancewi se, if you know, does the jet engine take a speed hit
for each additional 'inner join' a query has? IOW, is 6 'inner joins"
is 'x' amont slower than 3 inner joins?

Hm. No data. But I think that if your indexes are well defined, an inner
join can be significantly *faster* than a WHERE clause. Mine frequently are.

4. Does the order of the nested 'inner joins' make a difference to
performance? ( again, Ill play with it later, just curious for a quick
answer)

Absolutely. But the query optimizer will interfere with how you spell
the SQL statement. I was about to state "if you put a large dataset in
the inner substatements.. ." as I realized that there is nothing you can
say about the implementation of a given statement.

>
Thanks again for setting me on the correct path.

Hey, welcome!
--
Bas Cost Budde
Holland