help for a simple query.

[m***@gmail.com]

I have a table sector that has a relation with a table category.
Sector can have many categories so category has a foreign key sector_id that references sector.
If I do select * from category I can have the content of sector_id, for example 1, 2 and so on.
I want all categories and the sector name of the sector associated not the sector id.
Sorry for my english hope I've explained my problem well.

[Edgardo Portal]

Post by m***@gmail.com
I have a table sector that has a relation with a table category.
Sector can have many categories so category has a foreign key sector_id
that references sector.
If I do select * from category I can have the content of sector_id, for
example 1, 2 and so on. I want all categories and the sector name of the
sector associated not the sector id.
Sorry for my english hope I've explained my problem well.

Something like the following?

BEGIN TRANSACTION ;

CREATE TABLE sector (
id integer not null
,name text not null
) ;

CREATE TABLE category (
id integer not null
,name text not null
,sector_id integer not null -- FK to sector.id
) ;

ALTER TABLE sector
ADD PRIMARY KEY (id)
;

ALTER TABLE category
ADD CONSTRAINT fk_cat_sid
FOREIGN KEY (sector_id)
REFERENCES sector (id)
;

INSERT INTO sector VALUES(1, 'Aerospace') ;
INSERT INTO sector VALUES(2, 'Transportation') ;
INSERT INTO sector VALUES(3, 'Agriculture') ;

INSERT INTO category VALUES(1, 'category A', 3) ;
INSERT INTO category VALUES(2, 'category B', 3) ;
INSERT INTO category VALUES(3, 'category C', 2) ;
INSERT INTO category VALUES(4, 'category D', 1) ;

SELECT * FROM sector ;

SELECT * FROM category ;

SELECT category.*
,sector.name
FROM category
LEFT JOIN sector
ON sector.id=category.sector_id
ORDER BY category.id
;

ROLLBACK ;