This is more a SQL than a Derby question, but maybe someone can help me.
I have created a table with the following command:
CREATE TABLE CATEGORIAS
IDCATEGORIA SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY,
DESCCATEGORIA VARCHAR(30) NOT NULL,
CONSTRAINT CATEGORIASPK PRIMARY KEY (IDCATEGORIA),
CONSTRAINT CATEGORIASFK1 FOREIGN KEY (SUPERCATEGORIA) REFERENCES CATEGORIAS,
CONSTRAINT CATEGORIASUQ UNIQUE (DESCCATEGORIA)
As you can see, there's a foreing key that references to that same table. The idea is that a row can reference another row in the table as a "parent". It's for categories that can have 2 levels, like a tree structure of depth 2.
As it is now, a select * gives me this:
IDCATEGORIA DESCCATEGORIA SUPERCATEGORIA
1 Medio Ambiente null
2 Agua 1
I hope you understand how it works. It means (for me) that category Agua is included in Medio Ambiente (as SUPERCATEGORIA states)
Now, my question: I need to make a SELECT satement that returns 2 columns, DESCCATEGORIA, and the DESCCATEGORIA associated with the IDCATEGORIA that appears in SUPERCATEGORIA. For Example, it should return this for the items above:
Medio Ambiente null
Agua Medio Ambiente
I don't know how to make thar SELECT statement, or if I should use a VIEW, or what. Anything you can tell would be helpful, cause I'm quite new to SQL.
Stan 120000HAGM267 Posts
Re: Foreing key in the same table2007-09-17T19:59:43ZThis is the accepted answer. This is the accepted answer.Use a self join by referencing the table in the FROM clause twice - each reference should be aliased and then treated like different tables (e.g. a join must be specified). Example:
select a.SUPERCATEGORIA, a.DESCCATEGORIA, b.DESCCATEGORIA
from CATEGORIAS a, CATEGORIAS b
where a.SUPERCATEGORIA = b.SUPERCATEGORIA