How to select from more than one record of the same field in a JOIN-CodePudding

I have a table of letters. And table with authors and recipients called "people". I want to run a SQL command that returns a table or row with the letter and BOTH its author and its recipient.

Here's what I tried so far. If I need a table with letters and the author name I can use:

SELECT date, type, description, firstName, lastName
FROM letters
INNER JOIN people ON letters.authorId=people.ROWID

This gives me a table like the following:

1866-12-08  request need of women and children, list of needed supplies, FREEDMEN'S BUREAU  A.M.L.  Crawford
1867-01-18  request destitution of women and children in state and efforts to help  M.H.    Cruikshank

If I need recipient I can change the last line to:

INNER JOIN people ON letters.recipientId=people.ROWID

I want both the author and recipient. But I cannot seem to select more than one of the same field in a JOIN.

I have tried using the following.

SELECT date, type, description, firstName, lastName, firstName, lastName
FROM letters
INNER JOIN people ON letters.authorId=people.ROWID
INNER JOIN people ON letters.recipient=people.ROWID

This leads to the error:

Execution finished with errors.
Result: ambiguous column name: firstName
At line 1:
SELECT date, type, description, firstName, lastName, firstName, lastName
FROM letters
INNER JOIN people ON letters.authorId=people.ROWID
INNER JOIN people ON letters.recipient=people.ROWID

CodePudding user response：

When you JOIN to the same table more than once, as you do to show senders' and recipients' details, you must give the tables aliases.

FROM letters
INNER JOIN people senders ON letters.authorId=senders.ROWID
INNER JOIN people recips ON letters.recipient=recips.ROWID

Then you can mention columns from senders and recips in SELECT, WHERE, and other clauses as if those were separate tables.

SELECT letter.date, letter.type, letter.description, 
       sender.firstName, sender.lastName, 
       recip.firstName, recip.lastName 
       ...

Pro tip In queries with JOINs, always qualify each column you mention with the table name or alias. Say, for example, letter.description instead of just description. It makes your query much easier to understand for the next person. or even your future self.