CODEWITHSUNDEEP
phpmysqlsqldatabase
Thinker
Thinker

Reputation: 11

SQL: Show last messages from DB

I've a problem with SQL query. (Sorry, my english is so bad)

I'm developing a private-messages system for a website, and i have db like these:

thread

| ID | TITLE |

user_x_thread

| ID | ID_THREAD | ID_USER |

messages

| ID | ID_THREAD | ID_USER | MESSAGE | TIMESTAMP |

In thread there is a list of threads

In user_x_thread there is a list of user for each threads. I.E. in thread #1 there are user #2, #3

Finally, in messages there is a list of messages sended for each thread.

I would like to show all threads where user @Alex are signed, ordered by their last messages.

EXAMPLE

thread

| ID |        TITLE       |
| #1 |    Me and Marta    |
| #2 |   Marta and John   |
| #3 |     Me and John    |
| #4 | Me, Marta and John |

user_x_thread

| ID | ID_THREAD | ID_USER |
|  1 |     #1    |  Alex   |
|  2 |     #1    |  Marta  |
|  3 |     #2    |  Marta  |
|  4 |     #2    |  John   |
|  5 |     #3    |  Alex   |
|  6 |     #3    |  John   |
|  7 |     #4    |  Alex   |
|  8 |     #4    |  Marta  |
|  9 |     #4    |  John   |

@messages

| ID | ID_THREAD | ID_USER  |            MESSAGE          | TIMESTAMP |
| 1  |     #1    |   Alex   |          Lorem ipsum        |  21:35:45 |
| 2  |     #2    |   Marta  | Alex can't see this message |  21:35:58 |
| 3  |     #3    |   John   |           Hello.            |  21:36:10 |
| 4  |     #1    |   Marta  |            Demo.            |  21:36:35 |
| 5  |     #4    |   John   |        I like blue          |  21:36:47 |

RESULT

Hi Marta, you are signed in: (ordered by last message received)

Upvotes: 1

Views: 274

Answers (2)

Pranav
Pranav

Reputation: 8871

you can try like this :-

SELECT TD.TITLE,MS.MESSAGE,MS.TIMESTAMP from 
THREAD TD
JOIN user_x_thread UXT ON TD.ID=UXT.ID_THREAD
JOIN Messages MS ON MS.ID_THREAD=UXT.ID_THREAD
WHERE UXT.ID=@userid//say 2 or 3 
GROUP BY TD.ID,TD.TITLE,MS.MESSAGE,MS.TIMESTAMP
ORDER BY MS.TIMESTAMP DESC

@userid is your logged user id :

Upvotes: 1

Vijay
Vijay

Reputation: 8451

try out this...

Select * from thread t inner join
user_x_thread ut on t.ID= ut.ID_THREAD inner join
messages m on m.ID_THREAD=ut.ID_THREAD
where ut.ID_USER= user #2
group by t.ID
order by m.ID desc

pass value for ID_USER at user #2

Upvotes: 0

Related Questions