Multiple Joins

douceur

Hi, I've run into a problem trying to use multiple joins in a query. I'm going to simplify everything, but my question should still be intact when I'm finished...

Basically, I have 4 tables. I have a projects, tasks, messages, and time. Tasks are specific tasks within the projects, messages are the messages about a specific task, and time is just a generic time related to the task.

What I'm trying to do is select each task, the join the project name (within the projects table) to it, join the number of messages related to it, and join the smallest time related to it. The query I have now is something like:

SELECT    projects.projectName,
          tasks.taskID,
          COUNT(messages.messageID),
          MIN(time.time)
FROM      tasks AS tasks
LEFT JOIN projects AS projects
ON        tasks.projectID = projects.projectID
LEFT JOIN messages AS messages
ON        tasks.taskID = messages.taskID
LEFT JOIN time AS time
ON        tasks.taskID = time.taskID
GROUP BY  tasks.taskID

Now the thing is that when, for example, there is 4 messages and 3 times, COUNT(messages.messageID) shows up as 12. It multiplies them. The nearest I can figure is that the query is joining the tables to the previous one, and not necessarily to tasks. Is this true? If so, is there a way I can fix it?

Thanks.

ahundiak

A SQL Master might prove me wrong but I don't think you can do what you are trying to do with a simple query. You can probably do it with sub-queries and certainly with two queries.

The problem is that task has a 1 to many relation with messages and another 1 to many relation with time. So you end up getting one record for each task/message/time combo.

douceur

Originally posted by ahundiak
A SQL Master might prove me wrong but I don't think you can do what you are trying to do with a simple query. You can probably do it with sub-queries and certainly with two queries.

The problem is that task has a 1 to many relation with messages and another 1 to many relation with time. So you end up getting one record for each task/message/time combo.

I feared that was the case. To have it working now, I'm using two queries, but I thought maybe it was possible to simplify it to one.

Like you said, there's a 1-m relation with both messages and time, but I had hoped since I was grouping and using COUNT & MIN that it wouldn't matter.

Thanks for the reply.