Case statment with join-sql

I have a table User which has many messages and many contacts.

The relation between messages and contacts table is made trough contactMessages table.

Table messages has a foreign key on contactMessages which is the message_id

Table contacts has a foreign key on contactMessages which is the contact_id

Each message that a user is send is stored as a message with the from field the sender user, the reciever will be store the reference in the contactMessages table as contact_id, which will be used to look the contact and know to what user it has been send.

Now I want to select all the messages that a user has, sended and recieved.

I want to look up on if the id of the user that makes reference to that message is on the contactMessages table to find out if the user has send or recieved the message

I want to use it this way but I do not know how to query it, I guess that It may be trough a join but I am a novice with SQL and I am sstruggling to figure this out.

SELECT *,
CASE
    WHEN db.contacts.user_id = 1 THEN 'Inbox'
    WHEN user_id = 1 THEN 'Send'
END as type
FROM db.messages

User table:

+----+----------+--------------+
| id |   name   |    email     |
+----+----------+--------------+
|  1 | lluis    | lluis@eg.com |
|  2 | paco     | paco@eg.com  |
|  3 | ella     | ella@eg.com  |
+----+----------+--------------+

Contacts table:

+----+--------+
| id | userId |
+----+--------+
|  1 |      2 |
|  2 |      3 |
|  3 |      1 |
+----+--------+

contactsMessage table:

+------------+------------+
| contact_id | message_id |
+------------+------------+
|          3 |          1 |
|          3 |          2 |
|          3 |          3 |
|          1 |          4 |  contact_id references the reciever of the msg
|          2 |          5 |  which will be found in contacts table
+------------+------------+  table.contacts.id = 
                             table.contactsMessagecontact_id
                             Once that contact found contacts.userId = 1

messages table:

+----+--------+--------------+
| id | userId |   message    |
+----+--------+--------------+
|  1 |      1 | HI           |
|  2 |      1 | How are you? |
|  3 |      1 | Thanks       |
|  4 |      2 | Bye          |   userId makes reference to the sender of
|  5 |      2 | Hola         |   the message
+----+--------+--------------+

Ouput(messages table):

+----+--------+--------------+-------+
| id | userId |   message    | type  |
+----+--------+--------------+-------+
|  1 |      1 | HI           | Send  |
|  2 |      1 | How are you? | Send  |
|  3 |      1 | Thanks       | Send  |
|  4 |      2 | Bye          | Inbox |
+----+--------+--------------+-------+

This will get all the messages that the user with id = 1

Answer

I can guess next query:

select 
    messages.*,
    CASE
    WHEN contactsMessage.contact_id = 1 THEN 'Inbox'
    WHEN messages.userId = 1 THEN 'Send'
  END as type
from messages
join contactsMessage on contactsMessage.message_id = messages.id
where userId = 1 or contact_id = 1;

SQL fiddle here

Advertisement

Answer