Do i need a GROUP BY here

.NET
1

Hi,

I have the following SELECT which pulls out messages which are sent to users within a website.


"SELECT (hussaini_messages.type_id) AS IID, " +
            "(hussaini_messages.message_id) AS MID, " +
            "(hussaini_messages.recipient_id) AS RID, " +
            "(hussaini_messages.sender_id) AS SID, " +
            "(hussaini_messages.subject) AS Subject, " +
            "(hussaini_messages.message) AS Message, " +
            "(hussaini_messages.thread_id) AS TID, " +
            "(hussaini_messages.date_added) AS MSGDATE, " +
            "(hussaini_users.user_id) AS UID, " +
            "(hussaini_users.fname) AS Fname, " +
            "(hussaini_users.sname) AS Sname, " +
            "(hussaini_users.avatar) AS Avatar " +
            "FROM hussaini_messages LEFT OUTER JOIN hussaini_users ON " +
            "hussaini_messages.sender_id = hussaini_users.user_id WHERE hussaini_messages.recipient_id = @recipient_id OR hussaini_messages.sender_id = @recipient_id " +
            "ORDER BY hussaini_messages.date_added DESC";

Now the problem is that in my table hussaini_messages, i have multiple rows which relate to the same message. What this SELECT does is pull out every one.

I think i need to do something where i only pull out the 1. For example if i have 3 rows. 2 with message id of 1 and 1 with message id of 2. I have the problem becuase the message that links to message_id 2 is pulled out twice and displayed twice. So how can i pull out the one message?

I think its a GROUP BY but i dont know how to do it… :confused:

Regards
Billy

2

this is definitely not a GROUP BY situation

my advice is to find something unique about one of the multiple rows that “relate to the same message”

by the way, why are you wrapping your columns in parentheses?

3

well the message_id relates to the same message.I can have 3 messages which all have a message_id of 1. And 6 messages all with an message_id of 2 and vice versa…

So if this was the case how could i achieve my goal?

And by the way do u mean why am i doing this:-

(hussaini_messages.message_id) AS MID

Should i not be? :rolleyes:

Regards

4

achieve your goal by carefully examining the 6 messages to see if there is anything about one of them that would allow it to be identified apart from the others – some value in some column that is unique to that one

and yes, you don’t need the parentheses, they merely add more forest and make it harder to see the individual trees

5

well is the message_id not doing this already?

Each message is grouped together with the same message_id.

I will show u a typical row:-

type_id
message_id
recipient_id
sender_id
subject
message
thread_id
date_added

The above shows the table structure. The thread_id simply increments each time a reply is added. type_id is Auto increment.

Now lets say i have the following results.

1, 2, 14, 22, this is a test, hello i am testing…, 0, 26/01/2010 18:39:07
2, 2, 16, 22, this is a test, hello i am testing…, 0, 26/01/2010 17:45:01
3, 3, 16, 22, this is a test, hello i am testing…, 0, 26/01/2010 13:32:45
4, 3, 14, 22, this is a test, hello i am testing…, 0, 26/01/2010 16:11:25
5, 3, 14, 22, this is a test, hello i am testing…, 0, 26/01/2010 19:56:56

So do you see what i mean? 3 messages are grouped together with a message_id of 3 and the others with message_id of 2. So currently the select statement DOES pull out the correct messages but it pulls out ALL 5 whereas i only want to pull out 2 in this specific case. Then when i go into the message itself i can see the replies etc…

Hope you can help :slight_smile:

6

Which two? Is any one of the three ‘3’ messages ok? Or is there one in specific you want to extract?

7

Sorry my mistake the results were wrong. Here are the messages i work with:-

1, 2, 14, 22, this is a test, hello i am testing…, 0, 26/01/2010 18:39:07
2, 2, 16, 22, this is a test, hello i am testing…, 1, 26/01/2010 17:45:01
3, 3, 16, 22, this is a test, hello i am testing…, 0, 26/01/2010 13:32:45
4, 3, 14, 22, this is a test, hello i am testing…, 1, 26/01/2010 16:11:25
5, 3, 14, 22, this is a test, hello i am testing…, 2, 26/01/2010 19:56:56

So here lets say Person A sends a message to Person B. This is stored in the database where the message_id is 1 and the thread_id is 0. Then Person B replies to Person A. That data is also inserted into this table. However the thread_id is incremented by 1 and the message_id stays at 1. So now we have 2 messages one of which is a reply.

Now what is happening is in my “inbox.aspx” page. I only want to show the first message not all of them including the replies. I want to click on the first message which will take me to the replies…

But currently the select statement i am using pulls out ALL the messages, the more replies the more messages are pulled out. I simply was to pull out the FIRST sent message. This is why i have a timestamp. So lets say from these 3 messages:-

3, 3, 16, 22, this is a test, hello i am testing…, 0, 20/01/2010 13:32:45
4, 3, 14, 22, this is a test, hello i am testing…, 1, 26/01/2010 16:11:25
5, 3, 14, 22, this is a test, hello i am testing…, 2, 26/01/2010 19:56:56

I would want to show ONLY this mesage:-

3, 3, 16, 22, this is a test, hello i am testing…, 0, 20/01/2010 13:32:45

As it was sent first. So i need to use the TIMESTAMP also.

What would i need to do to my sql statement to achieve this?

Regards

8

No need to use the timestamp. The first message has always thread_id 0.
Just change your WHERE condition like this:


WHERE (hussaini_messages.recipient_id = @recipient_id OR hussaini_messages.sender_id = @recipient_id) +
AND   hussaini_messages.thread_id = 0 "
9

Yes thats perfect! :slight_smile:

Thank you so much.

I had another SQL query based question, can i ask u here or do i need to post another thread?

Regards

10

like thread_id = 0

sorry i wasn’t here to help you see the light, but guido did a great job in my absence

11

Its ok i have many other questions anyway…

Keep a look out :wink:

Regards

12

Hey Guys,

For some reason it seemed to have stopped working. The sql query i have here:-


SELECT (hussaini_messages.type_id) AS IID, " +
            "(hussaini_messages.message_id) AS MID, " +
            "(hussaini_messages.recipient_id) AS RID, " +
            "(hussaini_messages.sender_id) AS SID, " +
            "(hussaini_messages.subject) AS Subject, " +
            "(hussaini_messages.message) AS Message, " +
            "(hussaini_messages.thread_id) AS TID, " +
            "(hussaini_messages.date_added) AS MSGDATE, " +
            "(hussaini_users.user_id) AS UID, " +
            "(hussaini_users.fname) AS Fname, " +
            "(hussaini_users.sname) AS Sname, " +
            "(hussaini_users.avatar) AS Avatar " +
            "FROM hussaini_messages LEFT OUTER JOIN hussaini_users ON " +
            "hussaini_messages.sender_id = hussaini_users.user_id WHERE " +
            "hussaini_messages.recipient_id = @recipient_id OR hussaini_messages.sender_id = @recipient_id " +
            "AND hussaini_messages.thread_id = 0 " +
            "ORDER BY hussaini_messages.date_added DESC

It still pulls out all of the messages. I dont know why it is doing this…

I didnt change anything. Any ideas what may be wrong? :frowning:

Regards

13

parenthesize your ORs

the AND thread_id=0 condition is being appended only to the sender_id = @recipient_id condition

14

Do u mean like this?


SELECT (hussaini_messages.type_id) AS IID, " +
            "(hussaini_messages.message_id) AS MID, " +
            "(hussaini_messages.recipient_id) AS RID, " +
            "(hussaini_messages.sender_id) AS SID, " +
            "(hussaini_messages.subject) AS Subject, " +
            "(hussaini_messages.message) AS Message, " +
            "(hussaini_messages.thread_id) AS TID, " +
            "(hussaini_messages.date_added) AS MSGDATE, " +
            "(hussaini_users.user_id) AS UID, " +
            "(hussaini_users.fname) AS Fname, " +
            "(hussaini_users.sname) AS Sname, " +
            "(hussaini_users.avatar) AS Avatar " +
            "FROM hussaini_messages LEFT OUTER JOIN hussaini_users ON " +
            "hussaini_messages.sender_id = hussaini_users.user_id WHERE " +
            "(hussaini_messages.recipient_id = @recipient_id) OR (hussaini_messages.sender_id = @recipient_id) " +
            "AND hussaini_messages.thread_id = 0 " +
            "ORDER BY hussaini_messages.date_added DESC

I am selecting the messages that have the sender or recipient id of the user who is logged in.

@recipient_id is the id of the user who is logged in. This select statement currently pulls out all the messages and ignores the thread_id = 0…

Lets say these are my results:-

1, 2, 14, 22, this is a test, hello i am testing…, 0, 26/01/2010 18:39:07
2, 2, 16, 22, this is a test, hello i am testing…, 1, 26/01/2010 17:45:01
3, 3, 16, 22, this is a test, hello i am testing…, 0, 26/01/2010 13:32:45
4, 3, 14, 22, this is a test, hello i am testing…, 1, 26/01/2010 16:11:25
5, 3, 14, 22, this is a test, hello i am testing…, 2, 26/01/2010 19:56:56

where you can see 0,1,0,1,2 : that is the thread_id of a message. So lets say we look at one message:-

3, 3, 22, 14, this is a test, hello i am testing…, 0, 26/01/2010 13:32:45
4, 3, 14, 22, this is a test, hello i am testing…, 1, 26/01/2010 16:11:25
5, 3, 14, 22, this is a test, hello i am testing…, 2, 26/01/2010 19:56:56

the message_id is 3, and have been exchanged between 2 users wit ID’s of 22 and 14. So when the user clicks on inbox i want to SELECT the message with the thread_id of 0 which will be this:-

3, 3, 22, 14, this is a test, hello i am testing…, 0, 26/01/2010 13:32:45

But i dont get this result, i get all 3 results.

Any ideas why?

15

Look at the query I posted. I used the parenthesis the correct way.
Why did you eliminate them?

16

Ahh, sorry i must have removed them somehow :confused:

Well thanks again :lol:

Kind regards
Billy

17

Do you understand why they must be placed like that? And not the way you did?

18

well i think its because it does the WHERE clause in the brackets first and then checks for the thread_id = 0?

Is this correct?

19

do you also see the difference between the incredibly vital and important parentheses around the OR conditions, and the totally useless parentheses i was talking about earlier?

20

lol well im not too sure can u shed some light on this and why i dont need them?

I would really appreciate it.

Regards