It's been a while since I was working with SQL queries. Today I encounter a problem with fetching data from a table joined with referencing records from another table. I need support with constructing a valid SQL query, that will work with the SQLite database.

The problem

Given the following tables in SQLite database:

CREATE TABLE IF NOT EXISTS "contacts" (
  "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  "name" text NOT NULL
);

CREATE TABLE IF NOT EXISTS "messages" (
  "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  "date" integer NOT NULL,
  "senderId" integer NOT NULL,
  "recipientId" integer NOT NULL,
  "text" text NOT NULL
);

where:

• messages.senderId references contacts.id
• messages.recipientId references contacts.id

For contact with provided id, I would like to fetch all other contacts with the newest message that was either sent to or received from that contacts. The results should be ordered descending by the message date.

An example

For example, if we have the following contacts:

+----+------+
| id | name |
+----+------+
| 1  | A    |
+----+------+
| 2  | B    |
+----+------+
| 3  | C    |
+----+------+

and the following messages:

+----+------+----------+-------------+----------+
| id | date | senderId | recipientId | text     |
+----+------+----------+-------------+----------+
| 1  | 10   | 1        | 2           | A→B @ 10 |
+----+------+----------+-------------+----------+
| 2  | 20   | 2        | 1           | B→A @ 20 |
+----+------+----------+-------------+----------+
| 3  | 30   | 1        | 2           | A→B @ 30 |
+----+------+----------+-------------+----------+
| 4  | 40   | 1        | 3           | A→C @ 40 |
+----+------+----------+-------------+----------+
| 5  | 50   | 3        | 1           | C→A @ 50 |
+----+------+----------+-------------+----------+
| 6  | 60   | 2        | 3           | B→C @ 60 |
+----+------+----------+-------------+----------+
| 7  | 70   | 3        | 2           | C→B @ 70 |
+----+------+----------+-------------+----------+

The fetch result for the contact "A" should look like this:

+---------------+---------------+---------------+
| contacts.name | messages.date | messages.text |
+---------------+---------------+---------------+
| C             | 50            | C→A @ 50      |
+---------------+---------------+---------------+
| B             | 30            | A→B @ 30      |
+---------------+---------------+---------------+

The same fetch, but for the contact "B", should result in the following output:

+---------------+---------------+---------------+
| contacts.name | messages.date | messages.text |
+---------------+---------------+---------------+
| C             | 70            | C→B @ 70      |
+---------------+---------------+---------------+
| A             | 30            | A→B @ 30      |
+---------------+---------------+---------------+

And for the contact "C", we should get:

+---------------+---------------+---------------+
| contacts.name | messages.date | messages.text |
+---------------+---------------+---------------+
| B             | 70            | C→B @ 70      |
+---------------+---------------+---------------+
| A             | 50            | C→A @ 50      |
+---------------+---------------+---------------+

My failed attempt

Honestly, I feel lost and I'm not sure if what I'm doing is even close to the correct solution, but here is the SQL query I've tried to use:

WITH "lastMessage" AS (
  SELECT *, MAX("date") 
  FROM "messages" 
  GROUP BY "senderId" OR "recipientId"
) 
SELECT "contacts"."name", "lastMessage"."date", "lastMessage"."text"
FROM "contacts" 
JOIN "lastMessage" 
ON ("lastMessage"."senderId" = "contacts"."id") 
OR ("lastMessage"."recipientId" = "contacts"."id") 
WHERE "contacts"."id" != 1
ORDER BY "lastMessage"."date" DESC

Unfortunately, it doesn't work.

Any feedback will be much appreciated!