"sqlite3.OperationalError: ambiguous column name: main.person.id" -sqlite3

Hackerman 0
Viewed 24

I generate an SQL statement using python. When I print it out it looks like this:

SELECT * FROM item
LEFT JOIN person
ON item.owner_id = person.id
LEFT JOIN person
ON item.assignee_id = person.id

When I try to run my code, I get the following error (from Flask/Jinja):

sqlite3.OperationalError: ambiguous column name: main.person.id

An item can have an owner and an assignee. They can be different people. The data for the owners and the assignees come both from the table person.

In the end I want a statement that gives me an item including the owner and the assignee of it.

Just for your info: The original statement is a bit longer, I let out some stuff i saw as unnecessary for the question.

left-join sqlite sql
1 Answers

When you do self-joins, you have to alias the tables:

SELECT * FROM item
LEFT JOIN person AS p1
ON item.owner_id = p1.id
LEFT JOIN person AS p2
ON item.assignee_id = p2.id

Check the ON clauses. I assumed that this is what you meant, but I might be wrong.

PChemGuy0
Related