473,686 Members | 2,101 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL statement on inner join that hits another table twice?

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
Sep 19 '06 #1
6 9306
You don't say what you have tried. Include your best guess!
--
Bas Cost Budde
Holland
Sep 19 '06 #2
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 ***
Sep 19 '06 #3
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

Sep 19 '06 #4
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
Sep 19 '06 #5
>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
Sep 19 '06 #6
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
Sep 19 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3424
by: Stephen Patten | last post by:
Hi All, While in the process of building my table (40 or so Insert statments) can I then query ("select * from @Table_variable") and use the results up to theat point for another insert into @Table_varible? If you look for stepID -15 I have commented that section out due to it not retuning the correct values. Thank you in advance
4
8434
by: Steve Hall | last post by:
Folks, My secnario involves two tables - ObservationRegister, and Person. ObservationRegister contains most of the "useful" fields, including the UserID of the person that raised the record, and the UserID of the person to whom the record was assigned for action. I need to write a query to return all values in the ObservationRegister record, but instead of returning the UserIDs, I need to look up the actual name, by looking up the name...
1
4476
by: brett | last post by:
Here is my SQL string: "SELECT to_ordnum, to_orddate," _ & "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) * (DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ON DDPROD.pr_prodnum = DOBOM2.b2_prodnum INNER JOIN DDORD ON DOBOM2.b2_orid = DDORD.or_id INNER JOIN DDTORD ON DDORD.OR_TOID = DDTORD.TO_ID WHERE DOBOM2.b2_ordnum = ''order number here from result of outer select) AS Total" _ & "FROM DDTORD WHERE to_trak2id IN...
2
2518
by: serge | last post by:
/* This is a long post. You can paste the whole message in the SQL Query Analyzer. I have a scenario where there are records with values pointing to wrong records and I need to fix them using an Update statement. I have a sample code to reproduce my problem. To simplify the scenario I am trying to use Order related tables to explain a little better the tables i have to work with.
1
1451
by: KBuser | last post by:
Preface: I'm building an intranet site to build custom queries against our SQL Server (2000) db; The page is developed in ASP.net (2.0) with C# Codebehind. I dynamically generate and populate checkboxlists for each table that the user selects to query. I disable selecting non-related tables, and am in the process of creating inner joins when tables are related.... This is where I am getting stuck, here is the logic which builds the join...
9
2270
by: rhaazy | last post by:
Using MS SQL 2000 I have a stored procedure that processes an XML file generated from an Audit program. The XML looks somewhat like this: <ComputerScan> <scanheader> <ScanDate>somedate&time</ScanDate>
14
3094
by: Matt | last post by:
I need to add the following variable into an SQL statement and not sure how to do it. strGCID needs to be inserted into the following statement: SQL = "SELECT tblContacts.* FROM tblContacts INNER JOIN tblGC ON tblContacts.GCID = tblGC.gcID WHERE (((tblContacts.GCID)=strGCID))" i am just not sure of the proper syntax.
1
3621
by: Matt | last post by:
I am writing a DELETE statement and I want to filter the records using another SELECT statement. My SELECT statement is a GROUP BY query that grabs all social security numbers from the "Data With Import Date Current" table on a given day where there was only one transaction (count of SSN = 1). I want to delete these records from the "Data With Import Date Current" table. I would like to do this by joining the "Data With Import Date...
2
2830
ntxsoft
by: ntxsoft | last post by:
Hello everybody, I have a problem while I am trying to execute multiple query in a statement. My query like that DROP TABLE IF EXISTS query; CREATE TEMPORARY TABLE query SELECT Separations.ID,Name,Separation_Method,OrganismID,Date,Image,Description FROM Separations; DROP TABLE IF EXISTS login; CREATE TEMPORARY TABLE login SELECT Separations.ID,Name,Separation_Method,OrganismID,Date,Image,Description FROM Separations WHERE ID IN...
0
8517
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9057
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8934
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8780
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7603
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6441
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5800
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
2947
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
1940
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.