Reputation: 5718
I'm trying to formulate an SQL query for a messaging system that will return a list of all threads which have a status of 'OPEN' and whose last message was not posted by a user in a certain group.
The tables involved in the query are:
threads
-------
threadId
timestamp
subject
status
clientId
messages
--------
messageId
userId
messagebody
timestamp
threadId
users
-----
userId
username
groupId
The current query is:
SELECT threadId, timestamp, subject, status, clientId
FROM threads
WHERE status='OPEN'
ORDER BY timestamp DESC
which works fine; but I now have a new requirement - if the last message in the thread was posted by a user with a groupId
of 1 then it should NOT be in the result set.
Including the information I need is simple enough:
SELECT threadId, timestamp, subject, status, clientId
FROM threads
INNER JOIN messages USING(threadId)
INNER JOIN users USING(userId)
WHERE status='OPEN'
GROUP BY threadId
ORDER BY timestamp DESC
but at this point I get stuck, because I can't figure out how to get the query to filter out threads based on the messages.userId
with the highest timestamp
within a particular group. I've looked at HAVING
and all the aggregate functions and nothing seems to fit the bill.
Edit
I may not have been clear enough, so I'll try to illustrate with an example.
Here's some example data:
threads
threadId | timestamp | subject | status | clientId
--------------------------------------------------
1 | 1 | One | OPEN | 1
2 | 10 | Two | OPEN | 4
3 | 26 | Three | OPEN | 19
4 | 198 | Four | OPEN | 100
messages
messageId | userId | messagebody | timestamp | threadId
-------------------------------------------------------
1 | 3 | Hello, World| 1 | 1
2 | 1 | Hello | 3 | 1
3 | 1 | ^&%&6 | 10 | 2
4 | 2 | Test | 12 | 2
5 | 4 | Hi mum | 26 | 3
6 | 1 | More tests | 100 | 4
users
userId | username | groupId
---------------------------
1 | Gareth | 1
2 | JimBob | 2
3 | BillyBob | 2
4 | Bod | 3
In this example dataset threads 1 and 4 should be eliminated from the query because user Gareth
(a member of group 1) is the last one to post to them (messageId 2 in threadId 1, and messageId 5 in threadId 4). So the result set should only have the thread data (threadId, timestamp, subject, status and clientId) for threads 2 and 3.
I've created an SQLfiddle for this test data.
Can anyone point me in the right direction here?
Upvotes: 3
Views: 26822
Reputation: 51514
Does adding LIMIT 1
to your query solve your problem?
SELECT threadId, timestamp, subject, status, clientId
FROM threads
INNER JOIN messages USING(threadId)
INNER JOIN users USING(userId)
WHERE status='OPEN'
GROUP BY threadId
ORDER BY timestamp DESC
LIMIT 1
Or changing the where clause to
WHERE status='OPEN' and groupID<>1
I think this is what you want?
select * from threads
where threadId not in
(
select messages.threadId
from messages
inner join (select threadId, MAX(timestamp) maxtime from messages group by threadId) t
on messages.threadId = t.threadId
and messages.timestamp = t.maxtime
where messages.userId=1
)
Upvotes: 2
Reputation: 247880
Sounds like you want this:
SELECT threadId, timestamp, subject, status, clientId
FROM threads t
INNER JOIN messages m
ON t.threadId = m.threadId
INNER JOIN users u
ON m.userId = u.userId
and u.groupId != 1 --placed the groupId filter here.
WHERE status='OPEN'
GROUP BY threadId
ORDER BY timestamp DESC
Edit, this appears to give you what you need:
SELECT t.threadId,
t.timestamp,
t.subject,
t.status,
t.clientId
FROM threads t
INNER JOIN messages m
ON t.threadId = m.threadId
INNER JOIN users u
ON m.userId = u.userId
WHERE status='OPEN'
AND NOT EXISTS (select t1.threadid
FROM threads t1
INNER JOIN messages m
ON t1.threadId = m.threadId
INNER JOIN users u
ON m.userId = u.userId
where u.groupid = 1
and t.threadid = t1.threadid)
ORDER BY timestamp DESC
Upvotes: 2