SQLite foreign key to get value from another table

Question

so I hace 3 tables:

proveedor: _id descripcion

almacen: _id descripcion

Cab_ing _id descripcion cod_proveedor cod_almacen FOREIGN KEY(cod_proveedor) REFERENCES proveedores(_id) FOREIGN KEY(cod_almacen) REFERENCES almacenes(_id)

when I execute this query:

                 c = db.rawQuery(
                        " SELECT "
                        + "a."+KEY_ROWID + " AS _id,"
                        + "a.cod_agencia AS cod_agencia" + ","
                        + "a.fecha AS fecha" + ","
                        + "b.descripcion AS cod_proveedor" + ","
                        + "c.descripcion AS cod_almacen" + ","
                        + "a.estado AS estado" + ""
                        + " FROM " 
                        + "cab_ings a, proveedores b, almacenes c" 
                        +" WHERE a.cod_proveedor = b._id AND a.cod_almacen = c._id",null);

it doesn't show anything but WHEN I chance de "b._id" to '0' for example it shows. I dunno why, but I think is the reference problem.

EDIT:

I want the value (descripcion) of both tables thats why I use the traditional query a.id1 -> b.id AND a.id2 -> b.id

Proveedor table

ID      descripcion
1       R*1005
2       R*10335

Almacen table

ID      descripcion
3       a*10
6       a*12

Cab_ing table

ID      cod_proveedor   cod_almacen
1          1                 3
2          2                 3

Shouldn't it be; "where b._id = a.cod_proveedor and c._id = a.cod_almacen"? — john elemans, Dec 18 '15 at 22:29
Can you show a few lines of sample data? I'm confused about the search meaning. — john elemans, Dec 18 '15 at 22:33

score 0 · Answer 1 · answered Dec 18 '15 at 23:36

it might have something to do with the way you're referencing the primary keys of the Proveedor and Almacen tables. in your sample DDL the field are named ID, but in the join there's a reference to the _id field. (might that be referring to the alias in the select?)

i've created the following tables:

CREATE TABLE "Proveedor" ("ID" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "descripcion" VARCHAR)

CREATE TABLE "Almacen" ("ID" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "descripcion" VARCHAR)

CREATE TABLE "Cab_ing" ("ID" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "cod_proveedor" INTEGER, "cod_almacen" INTEGER)

... and subsequently inserted the same test data you specified above. using this query:

select 
    cab.ID as _id,
    pro.descripcion as cod_proveedor,
    alm.descripcion as cod_almacen
from 
    Cab_ing cab
        inner join Proveedor pro on cab.cod_proveedor = pro.ID
        inner join Almacen alm on cab.cod_almacen = alm.ID

... i get two rows returned:

ID=1, cod_proveedor=1, cod_almacen=3
ID=1, cod_proveedor=2, cod_almacen=3

well I'm managing cursors so my table id need to be the prefix '_ID' as said too when I change the "b._id" to 0 it show the data. If you see this post http://stackoverflow.com/questions/4313987/do-i-have-to-use-id-as-a-sqlite-primary-key-and-does-it-have-to-be-an-int-an it says that too — Alejandro Cabano, Dec 19 '15 at 00:02
yes I have two rows inserted in cab_ings and it shows in the listview when I change cab.cod_proveedor= 0 AND cab.cod_almacen = 0 — Alejandro Cabano, Dec 19 '15 at 00:15

SQLite foreign key to get value from another table

1 Answers1