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.
This topic has been locked.
1 reply Latest Post - 2007-09-17T19:59:43Z by Stan
Pinned topic Foreing key in the same table
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2007-09-17T19:59:43Z at 2007-09-17T19:59:43Z by Stan
Stan 120000HAGM267 PostsACCEPTED ANSWER
Re: Foreing key in the same table2007-09-17T19:59:43Z in response to SystemAdminUse 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