gray01 Posted May 11, 2003 Share Posted May 11, 2003 this is driving me mad, been trying to formulate one SQL query for 2 days now, please someone put me out of my misery! Doing a query on 2 tables:- messages table. ID // key field, int threadID // key field of thread this message is in title, body, etc. // stuff unreadmessages table ID // key field msgID // corresponds to the ID of a message record threadID // key field of thread this message is in userID // userID - indicates the user hasnt yet read the associatde message record. So thats a table of messages, added by various users. When a message is added, an unreadmessages record is created for all users apart from the one who has posted the new message. The query needs to be as follows:- Given a threadID and the userID of the user logging in, return all message records with that threadID. IF there is a corresponding unreadmessages record for that messageID and userID, return a value (indicating unread message) otherwise return value indicating message has already been read by this user, eg \'null\'. sounds simple enough but I just cant get it to work! At first I thought it\'d need a LEFT OUTER JOIN, but that would get all messages, not just for this thread. Maybe I need to nest 2 SELECT statements? Get a resultset of all messages (and get value from unreadmessages when there is a corresponding record), then produce a final table where only records with the right threadID are returned. hope I\'ve made that clear, I\'ve been stuck with this for a couple of days and a very tight deadline is looming up and I\'m going crazy-daisy. cheers Quote Link to comment https://forums.phpfreaks.com/topic/456-i-cant-formulate-a-sql-quey/ Share on other sites More sharing options...
holiks Posted May 11, 2003 Share Posted May 11, 2003 why don\'t you jst use one table for all messages with an extra field labeled sumthing like \"read\" and set unread msgs to 0 and read to 1 ...or yes and no? Quote Link to comment https://forums.phpfreaks.com/topic/456-i-cant-formulate-a-sql-quey/#findComment-1550 Share on other sites More sharing options...
gray01 Posted May 11, 2003 Author Share Posted May 11, 2003 the system can have many users, which is why a seperate table is necessary. A message may have been read by user 1, 3, 4 23 and 99, but not by user 2, 5, 6, and 22. I think I need to do some sort of nested SELECT? Do an OUTER JOIN on the messages table and select a field from the unreadmessages table when there is a match, and with the result from that query do another SELECT to get only messages for the thread I\'m interested in Quote Link to comment https://forums.phpfreaks.com/topic/456-i-cant-formulate-a-sql-quey/#findComment-1551 Share on other sites More sharing options...
gray01 Posted May 11, 2003 Author Share Posted May 11, 2003 whis would work if it was valid sql, which it isnt :-/ $query = \"SELECT * FROM (SELECT m.ID, m.threadID, m.title, m.body, m.timestamp, m.addedby, u.msgID FROM messages m LEFT OUTER JOIN unreadmessages u ON u.msgID = m.ID) WHERE m.threadID = \'$whichThread\'\"; Quote Link to comment https://forums.phpfreaks.com/topic/456-i-cant-formulate-a-sql-quey/#findComment-1552 Share on other sites More sharing options...
gizmola Posted May 13, 2003 Share Posted May 13, 2003 this is driving me mad, been trying to formulate one SQL query for 2 days now, please someone put me out of my misery! Doing a query on 2 tables:- messages table. ID // key field, int threadID // key field of thread this message is in title, body, etc. // stuff unreadmessages table ID // key field msgID // corresponds to the ID of a message record threadID // key field of thread this message is in userID // userID - indicates the user hasnt yet read the associatde message record. Speaking of headaches, I have a huge one at the moment, and I must confess I\'m not up to the task of scrutinizing your code, but I do have a kneejerk reaction to this design. IMNSHO, I feel you should have a table called msgread. When a reader reads a message, insert a row into this table, as oppossed to your current design. The first and most obvious reason for this is: 1. WHen you start you have let\'s say 10 users. There\'s let\'s assume 500 messages in the forum. Now user 11 signs up. Are you going to have the system go out and add Didnotread rows for all 500 messages? Ouch. Based on the way forums are used, I think it\'s a much better design, not to mention, one that spreads the transaction load, to simply add a row to a readmsg table when someone pulls up a message. Quote Link to comment https://forums.phpfreaks.com/topic/456-i-cant-formulate-a-sql-quey/#findComment-1570 Share on other sites More sharing options...
gray01 Posted May 13, 2003 Author Share Posted May 13, 2003 Thanks for the comments, I\'ve got this working now (though another SQL problem has arisen today). I know what you are saying, I had 2 choices. A table of read messages, which would grow at the same rate as the message table, or a table of unread messages, the size of which can range from zero to no_of_users * no_of_messages. So yes it would create 500 new records for a new user in a 500 message system. But then I did a little test and it only took a couple of seconds to create 100,000 records, which took up about 100K disc space. So my current option seemed the most intelligent, given that the unread table could shrink back to zero whenm all messages are read. I may well be proved wrong so I\'ll be keeping a close eye on performance, and now you\'ve mentioned it I may create a 2nd version with a read_message_table instead, just in case this design doesnt work and I need to swap it quickly! Quote Link to comment https://forums.phpfreaks.com/topic/456-i-cant-formulate-a-sql-quey/#findComment-1578 Share on other sites More sharing options...
gizmola Posted May 13, 2003 Share Posted May 13, 2003 Alas, Inserts on a Table locking transaction system with no contention isn\'t a very good test, although I commend your approach. Definately read this if you haven\'t. http://www.mysql.com/doc/en/Table_locking.html Quote Link to comment https://forums.phpfreaks.com/topic/456-i-cant-formulate-a-sql-quey/#findComment-1581 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.