Topic
1 reply Latest Post - ‏2007-09-17T19:59:43Z by Stan
SystemAdmin
SystemAdmin
1525 Posts
ACCEPTED ANSWER

Pinned topic Foreing key in the same table

‏2007-09-17T17:25:58Z |
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,
SUPERCATEGORIA SMALLINT,
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:

DESCCATEGORIA DESCSUPERCATEGORIA


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.
Updated on 2007-09-17T19:59:43Z at 2007-09-17T19:59:43Z by Stan
  • Stan
    Stan
    267 Posts
    ACCEPTED ANSWER

    Re: Foreing key in the same table

    ‏2007-09-17T19:59:43Z  in response to SystemAdmin
    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