Joining 3 tables from a database gives me an ambiguous column name error-CodePudding

I am trying to join 3 tables. Right now I'm just trying to run code in sqlite3 CLI but my ultimate goal is to run my query in python with .execute.

The .schema looks like this:

CREATE TABLE users (id INTEGER, 
    username TEXT NOT NULL,
    hash TEXT NOT NULL,
    cash NUMERIC NOT NULL DEFAULT 10000.00,
    PRIMARY KEY(id));
CREATE UNIQUE INDEX username ON users (username);

CREATE TABLE stocks (
    id INTEGER,
    symbol TEXT,
    PRIMARY KEY(id)
);
CREATE TABLE sqlite_sequence(name,seq);

CREATE TABLE transactions (
    id INTEGER NOT NULL UNIQUE,
    stockID INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    price FLOAT NOT NULL,
    userID INTEGER NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY (userID) REFERENCES users(id),
    FOREIGN KEY (stockID) REFERENCES stocks(id)
);

My query is in python and looks like this:

SELECT users.username, transactions.id, stocks.symbol 
FROM users
INNER JOIN users ON users.id=transactions.userID
INNER JOIN transactions ON transactions.stockID=stocks.id;

This gives me a Error: in prepare, ambiguous column name: users.username (1)

I have tried SELECT users.username FROM users; and that gives me all the usernames.

When I try to join two tables I get the same type of error.

I used W3 to learn joining tables but I don't understand where I am going wrong here.

CodePudding user response：

You have written your join query in the wrong way. I think you just need to swap the table's name. It would be like this

SELECT users.username, transactions.id, stocks.symbol 
FROM users
INNER JOIN transactions ON users.id=transactions.userID
INNER JOIN stocks ON transactions.stockID=stocks.id;

Try to run this query. I hope it will run correctly.