Re: message table

On 19-09-15 08:52, none wrote:
> Hi,
>
> I have a simple table that records messages sent between 2 users like:
>
> +----+------+----+--------+-------+
> | id | from | to | msg | date |
> +----+------+----+--------+-------+
> | 0 | A | B | "msg1" | date1 |
> +----+------+----+--------+-------+
> | 1 | A | C | "msg2" | date2 |
> +----+------+----+--------+-------+
> | 2 | C | B | "msg3" | date3 |
> +----+------+----+--------+-------+
> | 3 | A | B | "msg4" | date4 |
> +----+------+----+--------+-------+
> | 4 | D | A | "msg5" | date5 |
> +----+------+----+--------+-------+
>
> And so on…
>
> My issue is with having almost the same meaning in the fields "from" and
> "to".
>
> Getting all messages where 'A' is the sender is straightforward but what
> kind of construction would you advise me to get for example:
>
> - Every distinct user that has communicated one way and/or the other
> with A?
>

SELECT DISTINCT has_communicated_with_A FROM (

   SELECT DISTINCT `to` AS 'has_communicated_with_A' FROM table WHERE `from` = 'A'

   UNION ALL
   SELECT DISTINCT `from` FROM table WHERE `to` = 'A' )

> - And for each distinct one, the last message to or from 'A'?
>

If you have the list from the previous answer, you could create a VIEW from it.

 From there it should be possible to query what you want to have.

Provided the correct indexes are created, it might work quick, if no indexes exist on relevant columns it can get slow (with many messages)

> Thanks for any response/idea/hint!

BTW, `from` and `to` are both reserved words. I dont like columnname with have a name which is a reserved word..... ;) https://dev.mysql.com/doc/refman/5.7/en/keywords.html Received on Sat Sep 19 2015 - 10:26:46 CEST