CODEWITHSUNDEEP
mysqlsql
stefanhalbeisen
stefanhalbeisen

Reputation: 123

MySQL-Query - Can´t get the right result

Hello, you see here a screenshot of the table-structure, which I want to extract information from. It is a table from a messaging-system.

Two users, which write each other messages get a unique msgconv id.

Now, I would like to get the latest msg from each msgconv where a specific user is involved in, but the msg is not allowed to be deleted by the specific user.

This is my query at the moment: 

SELECT v1.msgconv
     , v1.timesent
     , v1.msg
     , v1.msgfrom
     , v1.msgto
     , v1.msgdel_sender
     , v1.msgdel_receiver
  FROM msg v1
  LEFT 
  JOIN msg v2
    ON v1.msgconv = v2.msgconv 
   AND v1.timesent < v2.timesent
 WHERE v1.msgfrom = 69 
   AND v1.msgdel_sender = 0
    OR v1.msgto = 69
   AND v1.msgdel_receiver = 0 
 ORDER 
    BY timesent DESC

With this query I get 14 results and I don´t know why. What I should get is only the latest msg of each msgconv.

Can please someone help me with this, thank you

Upvotes: 0

Views: 41

Answers (1)

GMB
GMB

Reputation: 222422

I would like to get the latest msg from each msgconv where a specific user is involved

If you are running MySQL 8.0, you can use ROW_NUMBER() to identify the records to display:

SELECT *
FROM (
    SELECT 
        m.*,
        ROW_NUMBER() OVER(PARTITION BY msgconv ORDER BY timestamp DESC) rn
    FROM msg m
    WHERE 
           ( msgfrom = 69 AND msgdel_sender = 0 )
        OR ( msgto = 69 AND msgdel_receiver = 0 )
) x

In earlier versions, you can use a NOT EXISTS condition with a correlated subquery:

SELECT *
FROM msg m
WHERE 
    ( 
           ( msgfrom = 69 AND msgdel_sender = 0 )
        OR ( msgto = 69 AND msgdel_receiver = 0 )
    )
    AND NOT EXISTS (
        SELECT 1
        FROM msg m1
        WHERE 
            (
                   ( m1.msgfrom = 69 AND m1.msgdel_sender = 0 )
                OR ( m1.msgto = 69 AND m1.msgdel_receiver = 0 )
            )
            AND m1.msgconv = m.msgconv 
            AND m1.timestamp > m.timestamp
    )

The query starts by selecting all records that belong to the relevant user. Then, the NOT EXISTS condition filters out every record but the latest in each group of record having the same msgconv.

Upvotes: 2

Related Questions