[SQL]Select same date from one row

I created 3 tables:

People:

ID INTEGER PRIMARY KEY,
SURNAME VARCHAR(15),

Car:

ID INTEGER PRIMARY KEY,
NAME VARCHAR(15),

Transaction:

ID INTEGER PRIMARY KEY,
ID_CAR INTEGER,
ID_BYUER INTEGER,
ID_SELLER INTEGER,
FOREIGN KEY (ID_CAR) REFERENCES SAMOCHODY(ID),
FOREIGN KEY (ID_BUYER) REFERENCES OSOBY(ID),
FOREIGN KEY (ID_SELLER) REFERENCES OSOBY(ID))

And when I add some value to transaction I have:

My people table:

| ID | SURNAME |
+----+---------+
| 1  |   Test  |
| 2  |  Smith  |

| ID |ID_CAR|ID_BYUER|ID_SELLER|
+----+------+--------+---------+
|  1 |  1   |   1    |     2   |

I tried use:

select id, surname, surname 
from transaction 
natural join people;

but I only got surname buyer.

I also try with group by but effects were same.

Answer

You could join on the people table twice – once for the seller and once for the buyer:

SELECT t.id, s.surname, b.surname 
FROM   transaction t
JOIN   people s ON t.id_seller = s.id
JOIN   people b ON t.id_buyer = b.id

Advertisement

Answer