You basically need to do the following (I'm using temporary tables to help me think through it - and because of the lack of views and sub-selects):
1) Find the groups each user belongs to
CREATE TEMPORARY TABLE user_groups
SELECT users.id AS id,
users.username AS username,
g-u.groupID AS group
FROM users INNER JOIN g-u ON users.id = g-u.userID;2) From there, you can find all the messages that belong to each user
CREATE TEMPORARY TABLE user_messages
SELECT user_groups.id AS id,
user_groups.username AS username,
user_groups.group AS group,
messages.id AS messageID
FROM user_groups INNER JOIN messages ON user_groups.group = messages.groupID;
3) From there, the tricky part is to find the messages that DON'T have replies. There are some tips in the mySQL documentation about JOINS (in the comments section) that might give some more details/information/clarification/examples/explanation about this. (note the left join)
SELECT user_messages.username, user_messages.messageID
FROM user_messages LEFT JOIN replies ON user_messages.messageID = replies.messageID
WHERE replies.messageID IS NULL;
That SHOULD give you a listing of all users names, and the messageID's they've not yet replied to. I obviously don't have a test bed, so I apologize for any typos or anything that doesn't work quite right. Hopefully this gives you what you need, or at least puts you on the right track.
After you're done, you're going to want to drop the temporary tables:
DROP TABLE user_groups; DROP TABLE user_messages;
Note that the temporary tables are only accurate at the instant you create them. They don't reflect changes until you drop and re-create them.
I'm not good enough with joins and whatnot to try to put more than two tables into one (don't even know if you can), but a total of five statements isn't bad, eh?
If you need more explanation about the how, why, or intent of any of the above, or if something doesn't work right, feel free to ask for clarification.
Good luck! -Cameron Wilhelm
On Tuesday, July 29, 2003, at 09:39 PM, Eric Winer wrote:
I've been browsing through posts here, but I haven't found anything relevant
to my issue, so I guess I'll just post it myself:
I have three tables: users, groups (which define collections of users),
messages, and replies (replies are completely different from messages and
warrant their own table). Each message is sent out to a single group of
users, and each user recieving the message can submit one reply. Here is
the relevant (simplified) table info:
messages -------------------- int id int userID (the person who sent the message) int groupID (the group that the message is sent to) text messageBody etc.
replies -------------------- int id int messageID (the message this is a reply to) int userID (the user who sent the reply) text replyBody etc.
users ------------------ int id text userName etc.
groups ---------------- int id text groupName etc.
g-u -------------- int groupID int userID
How can I write a query that returns the sender's username and message's id
for every message which a specified user has been sent but has not yet
replied to? If you could explain your answer reasonably, please do so - I
would like to learn how to write such a query so I don't have to bug the
mailing list again. I'm using mySQL 4.0.x, so I can't use subqueries.
Thanks!
-Eric Winer
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
