Jump to content

Archived

This topic is now archived and is closed to further replies.

gray01

I cant formulate a SQL quey :(

Recommended Posts

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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\'\";

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

×

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.