MySQL - Detail with a query

Question

MySQL - Detail with a query

Navigation

#1 by (2 votes)

5

I have a database called messaging , consisting of 3 tables

Table - users

Here the personal information of each user is stored.

 ______ _________ _________ ________
| idUs | nomUs   | emailUs | codeUs |
 ────── ───────── ───────── ────────
|   1  | Andrey  | [email protected] | 123456 |
 ────── ───────── ───────── ────────
|   2  | Paola   | [email protected] | 123456 |
 ────── ───────── ───────── ────────
|   3  | Felipe  | [email protected] | 123456 |
 ────── ───────── ───────── ────────
|   4  | Daniela | [email protected] | 123456 |
 ────── ───────── ───────── ────────

Table - texting_token

Here, unions of the user IDs that are chatting are made.

That is to say that for example : 1.2 = > Andrey & Paola

 ______ _____________
| idMT | Descripcion |
 ────── ─────────────
|   1  |     1.2     |
 ────── ─────────────
|   2  |     1.3     |
 ────── ─────────────
|   3  |     1.4     |
 ────── ─────────────
|   4  |     2.2     |
 ────── ─────────────

Table - messages

Each message is recorded here, including your TokenID to know who you are talking to.

 ______ __________ __________ ____________ _____________________ _________
|  id  | usuarioA | usuarioB | mensaje    | date                | TokenID |
 ────── ────────── ────────── ──────────── ───────────────────── ───────── 
|   1  |     1    |     2    |   Hola     | 2017-01-17 02:52:00 |    1    |
 ────── ────────── ────────── ──────────── ───────────────────── ───────── 
|   2  |     2    |     1    |   Hi!      | 2017-01-17 02:53:00 |    1    |
 ────── ────────── ────────── ──────────── ───────────────────── ───────── 
|   3  |     1    |     2    |   Qué más  | 2017-01-17 02:54:00 |    1    |
 ────── ────────── ────────── ──────────── ───────────────────── ───────── 
|   4  |     2    |     1    | Nada, aquí | 2017-01-17 02:55:00 |    1    |
 ────── ────────── ────────── ──────────── ───────────────────── ─────────

I have the following query:

SELECT 
   TokenID,usuarios.nomUs as destinatario,
   mensaje,
   date
FROM
   mensajes,usuarios
WHERE
   (usuarioA = ? OR usuarioB = ?) AND
   mensajes.usuarioA = usuarios.idUs AND
   usuarios.idUs <> ?
GROUP BY
   TokenID
ORDER BY
   mensajes.date DESC

What I want with the query is that, organize by date descendant , and that NO repeat the TokenID, as if it were DISTINCT but showing the other columns . The use of < > is so that the recipient is not "me" but always the other user.

My query does not work as I expect, it does not repeat TokenID but it selects the first message, that is. Instead of throwing:

 _________ ________________ ____________ _____________________
| TokenID |  destinatario  |   mensaje  |        date         |
 ───────── ──────────────── ──────────── ─────────────────────
|    1    | Paola Gonzales | Nada, aquí | 2017-01-17 02:55:00 |
 ───────── ──────────────── ──────────── ─────────────────────

It is resulting in:

 _________ ________________ ____________ _____________________
| TokenID |  destinatario  |   mensaje  |        date         |
 ───────── ──────────────── ──────────── ─────────────────────
|    1    | Paola Gonzales |    Hola    | 2017-01-17 02:52:00 |
 ───────── ──────────────── ──────────── ─────────────────────

I also want to be able to find the last messages of the other users. How can I solve my problem?

mysql sql

asked by Máxima Alekz 18.01.2017 в 07:59

source

1 answer

Obtain Maximum and minimum of an SQL Calculated query (2 columns). Server 2008 Why does my Keylogger in Python 3.6 save spaces and not the letters it registers?

score 2 · Accepted Answer

As I see it, what you need for the last message received is to consult directly as you say, with an order by. However, we must bear in mind that the best way to consult these tables is to determine which table is going to make the rhythm or main and which is the secondary.

In this case, the table that should be the main one is the message table and the secondary one, the user table, where only information will be taken

To get a certain number of messages it's just a matter of changing that 1 of the limit for the value you need

SELECT      MEN.tokenId 'TOKENID',
            USU.NOMUS 'DESTINATARIO',
            MEN.MENSAJE 'MENSAJE',
            MEN.DATE 'DATE'
FROM        mensajes  MEN
INNER JOIN  USUARIOS  USU
ON          MEN.USUARIOB = USU.IDUS
WHERE       (USU.IDUS = 1 OR USU.IDUS = 2)
order by    MEN.date desc limit 1