You have guessed the relationships correctly.
mySQL does run the first query without complaining, however it did throw an
error when I fed it your ammended query.
And actually looking at the data returned, I am beginning to think that it
is correct, although some of the data in some of the columns looks a bit
spurious. I'm going to have to spend a bit of time working out on paper what
I should be getting.
As for your doubts about project owners vs. task owners thing, the logic
behind it is this:

a person can be the 'owner' of a client, i.e the account manager.
That person can raise a project on that client and assign it either to
himself, or anyone else in the personnel table.
That project can then consist of 0 to many tasks and each task can be
assigned to either the project owner, or anyone else in the personnel table.

therefore I could have a task assigned to me where I am neither the project
owner or the client owner.
Likewise, I could be a client owner, but have no projects or tasks for that
client assigned to me, even though there are many tasks and projects for
that client.

I think that this maps quite well to the real world.

Dave

-----Original Message-----
From: Jeff Mason [mailto:jeffm.ma.ultranet@r...]
Sent: 10 August 2002 19:49
To: dave@w...
Subject: RE: [sql_language] RE: Problem with 3 table join.


I got your table DDL and data.

I have no experience with mySQL; it's virtually all with SQL Server.

The DDL wasn't quite compatible, but I was able to get a database set up
with your data.

I would prefer that we continue this discussion on the list, so others may
benefit from the conversation, or contribute as appropriate.  But before we
do, I can't help but notice that you do not have any referential integrity
constraints between the tables; thus it is difficult for me to determine the
proper relationships between the table, and your column names don't always
help...

Let me summarize my guess as to what the relationships might be (PK means
primary key, FK means foreign key):

clients.clientid=PK
	 .ownerID= FK -> Personnel.PersonID

personnel.PersonID=PK

projects.projectid=PK
	  .owner= FK -> Personnel.PersonID
	  .clientID= FK -> clients.clientID

tasks.taskID=PK
	.projectid = FK -> projects.projectid
	.owner = FK -> Personnel.PersonID			???? is this correct?
	.creator = FK ->Personnel.PersonID			???? is this correct and does it
matter?

Now, what were we trying to get out of this?

(I'm beginning to think that the notion of a person owning a task without
"going through" a project might be a bad thing...)

BTW, when I tried to run the query you had posted earlier,:

SELECT COUNT(DISTINCT projects.project_id) AS project_count, projects.owner
AS project_owner, " & _
"COUNT(DISTINCT tasks.task_id) AS task_count, tasks.owner as task_owner, "
&_
"personnel.person_id, personnel.surname, personnel.forename, " & _
"COUNT(DISTINCT clients.client_id) AS client_count, clients.owner_id " & _
"FROM projects " & _
"RIGHT JOIN personnel " & _
"ON projects.owner = personnel.person_id " & _
"LEFT JOIN tasks " & _
"ON tasks.owner = personnel.person_id " & _
"LEFT JOIN clients " & _
"ON clients.owner_id = personnel.person_id " & _
"WHERE tasks.progress_percent < 100 " &_
"GROUP BY personnel.person_id " & _
"ORDER BY project_count DESC, task_count"


I got several errors, since you were GROUPing things and several of the
columns in the select clause were not in an aggregate function or mentioned
in the group by clause.  I hope mySQL doesn't let you do this without
complaining...

I changed the query to:

SELECT COUNT(DISTINCT projects.project_id) AS project_count, projects.owner
AS project_owner,
		COUNT(DISTINCT tasks.task_id) AS task_count, tasks.owner as task_owner,
		personnel.person_id, personnel.surname, personnel.forename,
		COUNT(DISTINCT clients.client_id) AS client_count, clients.owner_id
	FROM projects RIGHT JOIN personnel ON projects.owner = personnel.person_id
		LEFT JOIN tasks ON tasks.owner = personnel.person_id
		LEFT JOIN clients ON clients.owner_id = personnel.person_id
WHERE tasks.progress_percent < 100
GROUP BY
personnel.person_id,projects.owner,tasks.owner,personnel.surname,personnel.f
orename, clients.owner_id
ORDER BY project_count DESC, task_count

and it did return 3 rows.  How many rows do you think it should return?
Note that SQL Server issues a warning that NULL values are being discarded
(COUNT (column) doesn't count columns with NULL in them).

(I'm beginning to think that the notion of a person owning a task without
"going through" a project might be a bad thing...)
--
Jeff Mason			Custom Apps, Inc.
Jeff@c...