CODEWITHSUNDEEP
androidsqlite
Rafael Martins
Rafael Martins

Reputation: 13

inner join ambiguous column name: id

I want to search in my db, but the error

ambiguous column name: id (code 1): , while compiling: SELECT placa, modelo, fabricante, cor FROM carros INNER JOIN carroCliente ON id = carro WHERE cliente = ?

shows up, what am I doing wrong? I've been searching all day and did not find anything here.

My method: 

public void buscarDados(){
        cursor = getDatabase().rawQuery("SELECT " + DatabaseHelper.Carros.PLACA + ", " + DatabaseHelper.Carros.MODELO + ", "
                + DatabaseHelper.Carros.FABRICANTE + ", " + DatabaseHelper.Carros.COR +  " FROM " + DatabaseHelper.Carros.TABELA_CARROS
                + " INNER JOIN " + DatabaseHelper.CarroCliente.TABELA_CARROCLIENTE +
                " ON " + DatabaseHelper.Carros.ID + " = " + DatabaseHelper.CarroCliente.ID_CARRO
                + " WHERE " + DatabaseHelper.CarroCliente.ID_CLIENTE + " = ?", new String[] {Integer.toString(buscarIdCliente())});

        if(cursor!=null){
            cursor.moveToFirst();
        }
    }

Upvotes: 1

Views: 1549

Answers (4)

Aakash Patel
Aakash Patel

Reputation: 557

Try to give each table a name like 

SELECT st.id FROM student st INNER JOIN attendance at ON at.id = st.id;

Something like this. So, it can be identified that which table's id you are trying to access. If you get confused, than try preparing query in and DBMS tool first, than write code for that query.

Upvotes: 1

akhilesh0707
akhilesh0707

Reputation: 6899

Use Reference for both the tables, both tables have id columns ambiguous column name: id (code 1) error 

public void buscarDados(){
        cursor = getDatabase().rawQuery("SELECT A." + DatabaseHelper.Carros.PLACA + ", A." + DatabaseHelper.Carros.MODELO + ", A."
                + DatabaseHelper.Carros.FABRICANTE + ", A." + DatabaseHelper.Carros.COR +  " FROM " + DatabaseHelper.Carros.TABELA_CARROS
                + " as A INNER JOIN " + DatabaseHelper.CarroCliente.TABELA_CARROCLIENTE +
                " as B ON A." + DatabaseHelper.Carros.ID + " = B." + DatabaseHelper.CarroCliente.ID_CARRO
                + " WHERE B." + DatabaseHelper.CarroCliente.ID_CLIENTE + " = ?", new String[] {Integer.toString(buscarIdCliente())});

        if(cursor!=null){
            cursor.moveToFirst();
        }
    }

Upvotes: 2

Dr. X
Dr. X

Reputation: 2920

You have to use syntax as gicen below;

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

In your example ;

public void buscarDados(){
        cursor = getDatabase().rawQuery(
        "SELECT DatabaseHelper.Carros.PLACA, DatabaseHelper.Carros.MODELO, "
                + "DatabaseHelper.Carros.FABRICANTE , DatabaseHelper.Carros.COR FROM DatabaseHelper.Carros.TABELA_CARROS "
                + " INNER JOIN  DatabaseHelper.CarroCliente.TABELA_CARROCLIENTE  ON " 
        + " DatabaseHelper.Carros.ID = '" + DatabaseHelper.CarroCliente.ID_CARRO + "' "
                + " WHERE DatabaseHelper.CarroCliente.ID_CLIENTE  = '", new String[] {Integer.toString(buscarIdCliente())} + "'" ); 
       if(cursor!=null){
            cursor.moveToFirst();
        }
    }

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

As @Akhilesh mentioned in his comment, your error is probably being caused due to both tables having an id column defined. You can get around this by using explicit aliases to limit the scope of each column:

SELECT
    t1.placa,
    t1.modelo,
    t1.fabricante,
    t1.cor
FROM carros t1               -- explicitly specify which 'id' column you are referring to
INNER JOIN carroCliente t2
    ON t1.id = t2.carro
WHERE t2.cliente = ?

The other answers gave Java code, but I think a raw query is actually more appropriate here because it shows how you can use aliases to fix your problem. Actually, it is always a good idea to use aliases in your query, because it makes the query less terse and easier to read and understand.

Upvotes: 2

Related Questions