Jump to content

gray01

New Members
  • Posts

    6
  • Joined

  • Last visited

    Never

Profile Information

  • Gender
    Not Telling

gray01's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. 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!
  2. 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\'\";
  3. 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
  4. 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
  5. thanks biopv - i hope you get paid for technical support
  6. I\'ve got a few tables with auto-increment IDs. I want to delete all data and start again with empty tables. However, the auto-increment value is remembered, so the empty table isnt populated starting with ID = 1, but with the value before the data was deleted - is there a way to reset it?
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.