ID and firstName of persons who have sent a private message to another person who has the same firstName

Question

The asterisked attributes in the relational database schema code below are primary keys.

The publicMessage entity stores the messages that were transmitted in virtually public place (i.e. a WhatsApp group).

The privateMessage entity stores the private messages users sent privately and individually one to each other. pID1 represents the senders whereas pID2 represents the receiver.

person(**ID**, firstName, lastName, nickName)
follows(**pID1**, **pID2**)
publicMessage(**ID**, pID, date, text)
privateMessage(**ID**, pID1, pID2, date, text)

I would like to write an SQL query that lists the ID and firstName of persons who have sent a private message to another person who has the same firstName. The query should retrieve only the persons who sent a private message either to themselves or to other receivers who posses the same firstName.

SELECT DISTINCT p1.ID, p1.firstName
FROM person p1
JOIN privateMessage pm ON p1.ID = pm.pID1
JOIN person p2 ON pm.pID2 = p2.ID
WHERE p1.firstName = p2.firstName
OR pm.pID1 = pm.pID2;

I used the DISTINCT keyword to eliminate name repetitions.

I used the JOIN clause to connect the person table with the privateMessage table twice (as p1 and p2) based on the sender (pID1) and receiver (pID2) IDs.

The WHERE clause ensures that I only include rows where the first names match between the sender (p1) and receiver (p2) or if the sender is sending a private message to themselves (pm.pID1 = pm.pID2).

What are mistakes in the query?

What is a more elegant/efficient code which retrieves more effectively?

Answer 1

I did pretty much the same way as you did without using distinct. This is on PostgreSQL 15.

create table person(
    id INT generated always as identity primary key,
    firstName  varchar(12),
    lastName varchar(12),
    nickName varchar(12))
    ;

create table privateMessage(
    id INT generated always as identity primary key,
    sender_id integer, 
    receiver_id integer, 
    date date, 
    message_text varchar(32),
    constraint fk04
       foreign key(sender_id) references person(id),
    constraint fk05
       foreign key(receiver_id) references person(id)
    
    ) 
    ;

Create some people...

insert into person (firstName, lastName, nickName) values ('Alice', 'Smith', '');   /* pk 1 */
insert into person (firstName, lastName, nickName) values ('Bob', 'Jones', '');       /* pk 2 */
insert into person (firstName, lastName, nickName) values ('Alice', 'Young', '');     /* pk 3 */

Create a few messages:

insert into privateMessage (sender_id, receiver_id, message_text) values (1, 1, 'message text 1');
insert into privateMessage (sender_id, receiver_id, message_text) values (1, 2, 'message text 2');
insert into privateMessage (sender_id, receiver_id, message_text) values (1, 3, 'message text 3');
insert into privateMessage (sender_id, receiver_id, message_text) values (2, 1, 'message text 4');

Query the data:

select 
    p1.firstName || ' ' || p1.lastName as sender_name,
    p2.firstName || ' ' || p2.lastName as receiver_name,
    pm1.message_text as message_text
from
    person p1,
    person p2,
    privateMessage pm1
where
    p1.firstName = p2.firstName and
    pm1.sender_id = p1.id and
    pm1.receiver_id = p2.id
;

Results:

sender_name     receiver_name   message_text
Alice Smith     Alice Smith     message text 1
Alice Smith     Alice Young     message text 3