Help combining two queries... Nested Join? Subquer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help combining two queries... Nested Join? Subquer

Author

Topic

AdamKosecki
Starting Member

18 Posts

Posted - 2005-12-09 : 00:13:21

A ticket contains many activities. There is a join table that handles the many to many relationship of tickets and activities (ticket_activity_J).

Consider the following queries:
The first will return all of the "tickets" I need.
The second will return the date of the most recent activity for a given ticket.

I am having trouble creating a query that will return all of this information. In other words, I need to include "MAX(a.cdate)" in the first SELECT statement.

If this is not clear, let me know! Thank you.

SELECT
t.ticketid,
t.title,
t.completed,
t.cdate,
t.cuser
FROM
tickets t
WHERE
customerid = variableForCurrentID

-------------------------------------------

SELECT
MAX(a.cdate) AS lastActivity
FROM
activities a
INNER JOIN
ticket_activity_J j
ON a.activityid = j.activityid
WHERE
j.ticketid = variableForCurrentTicket

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-09 : 00:26:28

The below are not tested.

SELECT  t.ticketid, t.title, t.completed, t.cdate, t.cuser, max(a.cdate) as lastActivity
FROM    tickets t
inner join   ticket_activity_j j
        on   t.ticketid = j.ticketid
inner join   activities a
        on   a.activityid = j.activityid
where   t.customerid = variableForCurrentID
group by t.ticketid, t.title, t.completed, t.cdate, t.cuser

Is it OK ?

-----------------
[KH]
Guys, where are we right now ?

AdamKosecki
Starting Member

18 Posts

Posted - 2005-12-09 : 01:09:11

It works and makes perfect sense. Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources