I'm learning SQL and Microsoft Access at the same time. I have a
database dump that I'm importing into Access & analyzing, and need to
do a certain type of query. I'll simplify the database structure for
this question.
 
Employee 
-------- 
ID 
Name 
 
Team 
----------- 
ID 
ManagerA (employee.id) 
ManagerB (employee.id) 
 
TeamMembers 
----------- 
TeamID 
EmployeeID 
 
I'd like, first, a simple list of teams as follows:  
TeamID, ManagerA name, ManagerB name 
 
Then I want a simple list of team members as follows: 
Employee Name, TeamID, ManagerA, ManagerB 

Caveat 1:

The "Employee.ID" field contains a large number of zero values, which
contain data for contractors. No employee ID, but they have other
useful employee data in the system, and ways of referencing their
data.

Caveat 2:

The Teams table contains some teams with no Manager A, some with no
Manager B, and some with neither. A missing value is indicated with a
zero.

Caveat 3:

I did not model this data, and have no control over its structure. It
is actually from a large database from a large church, and I'm simply
getting a set of flat-file exports to work from for my analysis work.
The data is structured the way it is for, one hopes, good reasons. I
was a bit surprised myself to see multiple zero values in the
supposedly key field, but they have a big database system built around
this structure that seems to work.

Problem:

I want exactly one row for each Team for the first report.

If I do a basic join, I end up with more than one row for each row in
the Teams table. The zero values in the Teams table end up matching
several rows in the Employee table, and vice versa.

What I need, I think (as a rank amateur), is a query to eliminate
zero-values from employees, then a set of two left join operations:
one to left join the Team.ManagerA to the filtered employees table,
then a second to left join Team.ManagerB to the filtered employees
table.

The reason for the query ('where Employee.ID > 0') is to eliminate the
multiple zero values in the ID field.

The reason for the LEFT JOINs is to preserve the unmatched rows in the
Team table.

That way, I'd be guaranteed exactly one and only one result row for
each row in the Teams table. I'll get a row even if the team has no
managers, which is good.

The problem is, I can't figure out the proper SQL syntax. That is:
1) query employees for >0 value in ID
2) look up Team.ManagerA name in 1) above 
3) look up Team.ManagerB name in 1) above

The syntax I attempted was:
SELECT T.*, E1.name as "Manager A", E2.name as "Manager B"  
FROM Team T LEFT JOIN (select * from Employee E1 where E1.ID > 0) ON
(T.ManagerA = E1.ID)
LEFT JOIN (select * from Employee E2 where E2.ID >0) ON (T.ManagerB =
E2.ID)

When I try this, I get a non-helpful syntax error. I know there must
be a way of expressing this kind of query in SQL.

So, perhaps my question is best phrased as:

1) What is wrong with my query syntax above?

2) In general, how can I use the results of a query as a "table" in
yet another query? Do I have to do it with two separate queries where
the first query creates a "phantom" table (or whatever its called in
SQL)? Or is there a way to do it within a single extended SQL
statement?

3) In general, how do I do "parallel left joins" (my terminology)? How
can I left join Table2.A to Table1.ID and join Table2.B to Table1.ID
to end up with one lovely table containing all rows from Table2 with
"lookup values" from Table1?

Thank you,

--Vaughn

Hi Vaughn,

You are certainly jumping in at the deep end for a beginer. These
types of Queries can get very complicated very quickly as you have no
doubt seen. I'll try and step you through the complexities of nested
queries.

1/ The reason that the above query doesn't work is that it is an
extremeley simplified version of what you actually want to do. Access
and SQL treat the sub queries as seperate SQL statements, if you look
closely at the statement above, you are trying to use values from the
nested queries outside of those nested sub-queries. You are
effectively telling Access to use the Employee.Name (E1.Name) from
inside a sub query that you haven't declared an alias for. You have
declared the alias for the WHOLE Employee table, not your sub-query.

To create an alias for your sub-query, you need to write your SQL as:

SELECT E1.Name 
FROM [SELECT * FROM Employee WHERE ID > 0]. AS E1

2/ The correct syntax of the query you require is:

SELECT Team.TeamID, E1.Name AS Manager1, E2.Name AS Manager2
FROM
(Team LEFT JOIN [select * from employee where ID >0]. AS E1 ON
Team.ManagerA = E1.ID)
LEFT JOIN [select * from employee where ID >0]. AS E2 ON Team.ManagerB
= E2.ID;

This is the result using nested sub-queries, and this will work fine.
However, I would always recommend that you create 2 queries, one to
extract your non-zero ID's from the Employee table and the other to
build your final query. The benefit of this is that you ensure the
order in which the queries are processed, you are less likely to make
a mistake (1 "Select * from Employee where ID > 0" query instead of
typing it all twice) and readability of your overall queries is
improved.

You can easily do this by creating a query in MSAccess or a View in
SQL server and then using your query as a "table" in your final query:

qryNonZeroEmployees
SELECT * from Employee WHERE ID > 0

qryTeamManagers
SELECT T.*, E1.Name as Manager1, E2.Name as Manager2 
FROM (Team T INNER JOIN qryNonZeroEmployees E1 ON T.ManagerA = E1.ID)
INNER JOIN qryNonZeroEmployees E2 ON T.ManagerB = E2.ID)

3/ The above two answers should answer your 3rd question, you had
exactly the right idea in mind, just your implementation was a little
off! As I said above, I would lean towards having 2 seperate queries,
but ultimatly you come down to the same thing.

Hope this is helpful.

Molloch

---

Request for Answer Clarification by vaughn-ga on 03 Sep 2002 09:36 PDT

Thanks, this is exactly what I needed. It works perfectly.

Re: "you are certainly jumping in at the deep end for a beginner" --
Yes, I guess I am. I seems I have a gift for hitting the most complex
problems out of the gate. :-)

If you don't mind a bit of additional clarification: The subselect
statement has a period at the end of the closing bracket. The query
doesn't work without that period. My SQL books don't seem to mention
it.

1) What is that period for, & when is it used?
2) When do you use brackets vs. parenthesis?

---

Clarification of Answer by molloch-ga on 03 Sep 2002 16:48 PDT

Hi again,

Glad its all working for you.

Square brackets are used when the data enclosed in them is an element
of the query you are trying to write. In this case the whole sub-query
was an element of the larger query. Another example is the word
"date", in Access and SQL server DATE is a function which returns
todays date, however many people use Date as a column name in their
table. If you wrote the query:

SELECT date from tblEmployment

You would get an error. However:

SELECT [date] from tblEmployment

will work because it is treating [date] as an element of the query
rather than a function.

Normal brackets "()" are used as they are in math equations, to encase
specific functions and help with the process order of the query
itself:

SELECT (WageRate * (Hours Worked - BreakTime)) FROM tblWages

will work because Breaktime is subtracted from Hours Worked before it
is multiplied by the wage rate. Without the brackets the WageRate is
multiplied to the HoursWorked and then the BreakTime is subtracted.

This loosely translates to your query where the only brackets are
around one of the joins (the first one). This ensures that that join
is worked seperatly from the other join and gives "parralel" running
of the join rather than a "sequential" effect.

If you want a section of a query to run first, use normal brackets.
If what you are doing is to be an element of the query that will be
used by another part of the query, use square brackets.

The period after the sub query denotes to Access that the element is a
recordset (like a table) rather than a single value variable. Think of
the sub query as being a table within your query, the period to the
right of the square brackets signifies this. Just as if you had a
normal table you write [tablename].[fieldname], Access knows that the
element to the left of the period is the table and to the right is the
field. If you were to write .[Employee] it would expect a field called
employee, writing [Employee]. it would expect a table called employee.

Access does have some differences to other SQL languages, its concepts
are pretty good and the best way to learn is to build the query with
the query builder and then select the SQL button from the toolbar to
see what it has generated. It loves to use brackets, so you will find
they are everywhere which can confuse you at first. Also some SQL only
functions must be written in the SQL view.

Good luck and I'm glad I could help you.

Molloch.

Very clear, helpful, instructive, and most importantly of all, working.