Hi vaughn-ga,
These are both relatively simple queries, so I will just provide them
for you below:
QUERY #1:
--begin query
SELECT T.ID AS TeamID, E.Name, E2.Name
FROM Employee E, Employee E2, Team T
WHERE E.ID = T.ManagerA AND E2.ID = T.ManagerB
--end query
The trick with this one is to use two copies of the Employee table so
that you can place conditional statements (in the WHERE clause) for
each of managerA and managerB individually.
Query #2:
--begin query
SELECT E.Name, M.TeamID, T.ManagerA, T.ManagerB
FROM Employee E, Team T, TeamMembers M
WHERE E.ID = M.EmployeeID AND M.TeamID = T.ID
--end query
This one is not too difficult, as long as you remember to "link" all
of the tables with the equality conditions in the WHERE clause.
That should take care of the problem...good luck with your SQL
endeavours :)
Cheers!
answerguru-ga |
Request for Answer Clarification by
vaughn-ga
on
02 Sep 2002 15:33 PDT
Thanks for the answer. Yes, this does work, but I forgot to include an
important piece of information. I apologize for my incomplete
question.
1) For the first query, I would like the list to have exactly one row
for each row in the Team table.
2) For the second query, I need exactly one row for each non-contract
employee. For contractors, they can't be team members (since they are
not employees and have no employee number), so it should skip them, or
maybe show "none".
Two complications:
a) sometimes, there is no ManagerA (value of zero) or no Manager B or
even both.
b) the Employee table ID is not truly unique. It is for employees, but
it also contains non-unique data on contractors (ID value of zero for
all contractors).
So, what I need is a list of all teams with corresponding manager
names. If the manager ID is zero, it just puts "none" or "" or
something like that, instead of showing that team several times with
each contractor name (since it matches the ID of zero with a bunch of
contractors).
What I had tried to do is use a left outer join. I thought,
conceptually, I'd do a query on the Employee table for "ID greater
than zero". I'd then left join Team.ManagerA to copy 1 of that table,
and left join Team.ManagerB to copy 2 of that table. But I can't
figure out the syntax to do that. Here's what I tried:
SELECT T.*, E1.name as "Manager A", E2.name as "Manager B"
FROM Team T LEFT JOIN Employee E1 ON (T.ManagerA = E1.ID)
LEFT JOIN Employee E2 ON (T.ManagerB = E2.ID)
;
That was supposed to do the two-level join, but it doesn't work
(syntax error).
Then the next step was to filter out the ID=0 contract employees. I
tried to that like this:
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)
;
But this is the wrong syntax also. Your help is appreciated for this
SQL doofus. :-)
Thank you,
--Vaughn
|
Clarification of Answer by
answerguru-ga
on
02 Sep 2002 17:54 PDT
Hi,
For the first query, it should output one row for each Team, as long
as a ManagerA and ManagerB are found for that team. You can change:
WHERE E.ID = T.ManagerA AND E2.ID = T.ManagerB
to
WHERE E.ID = T.ManagerA OR E2.ID = T.ManagerB
This will list all teams with at least one manager.
For the second query, it seems that you have designed the tables
incorrectly becuase tables are meant to group similar entities, and
obviously employees and contractors have different properties and
should not be grouped together. The way you have it now, SQL cannot
differentiate them so this query is not feasible. It's also worth
noting here that SQL is meant to retrieve one of two types of data:
1. Data directly stored in the database
2. Relatively simple calculations on number data (not string values)
So if you want values like "none" in your result, that is what you
should enter in the appropriate place rather than leaving it blank.
SQL is not a full-featured programming language, and therefore does
not support if/else statements in queries.
If you design the tables effectively and enter data correctly you
won't need to worry about JOIN commands at all. Since this is getting
far off base from the original question, I suggest you post another
question describing:
1. What you are trying to model
2. The business rules you are following currently
3. The way the data is currently being stored
4. What queries you want to be able to do on the data (be specific
here to avoid the confusion that occurred on this question)
Keep in mind that this is a lot more time-consuming so you should set
the price accordingly if you choose to post this type of question.
Thanks for using Google Answers!
answerguru-ga
|
Request for Answer Clarification by
vaughn-ga
on
02 Sep 2002 20:01 PDT
I'm beginning to see that perhaps my question is not as easy to answer
as I'd hoped. So, I'll post another question with another price,
continuing this thread.
I'll also post my follow on here, in case it is easy to answer, and
for the benefit of others following this thread.
Clarification:
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.
That being said, the "Employee.ID" field does contain 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.
At the same time, the Teams table does contain some teams with no
Manager A, some with no Manager B, and some with neither. A missing
value is indicated with a zero.
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 for your kind assistance,
--Vaughn
|
Clarification of Answer by
answerguru-ga
on
02 Sep 2002 20:16 PDT
No new content is being added to this thread...for responses related
to the upcoming question please refer to Question ID: 61108.
Thanks for using Google Answers
answerguru-ga
|
,
0 Comments
)