I have two tables

CREATE TABLE "Documents"
(
"_fkContents" INTEGER NOT NULL,
"_fkUser" VARCHAR(5) COLLATE "ANSI_CI" NOT NULL,
"_Document" CLOB COLLATE "ANSI_CI" COMPRESSION 6,
"_WG" VARCHAR(3) COLLATE "ANSI_CI" COMPUTED ALWAYS AS 'WPT'   NOT NULL,
CONSTRAINT "PK" PRIMARY KEY ("_fkContents", "_fkUser")
)

CREATE TABLE "Contents"
(
"_ID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"_Book" VARCHAR(30) COLLATE "ANSI_CI" DEFAULT '',
"_Chapter" VARCHAR(30) COLLATE "ANSI_CI" DEFAULT '',
"_Topic" VARCHAR(30) COLLATE "ANSI_CI" DEFAULT '',
"_Link" INTEGER DEFAULT 0,
"_Title" VARCHAR(50) COLLATE "ANSI_CI" DEFAULT '',
"_CanEdit" BOOLEAN DEFAULT FALSE,
CONSTRAINT "PK" PRIMARY KEY ("_ID")
)

I want to select and display from the documents table eg

SELECT _fkContents FROM Documents WHERE _Document CONTAINS 'geronimo'

BUT and its a big but for me I want to show information from the Contents table. What I'd like for each row is:

_fkContents, _Book, _Chapter, _Topic, _Title, title for book, title for chapter

There are indices on _Book, _Chapter, _Topic and there will always be an entry for a book where _Chapter is '' and _Topic is '' and for a chapter where _Topic is ''

I'm trying to shoehorn it into a single SQL statement and failing dismally. Before I give up and use a mixture of SQL and Delphi is it achievable and can anyone do it?

Hope the explanation is good enough.

Roy Lambert

Went out, bought some more SF books and sused it:


SELECT
_ID,
_Book,
(SELECT _Title FROM Contents X WHERE X._Book = Y._Book AND X._Chapter = '' AND X._Topic = '') AS _xBookTitle,
_Chapter,
(SELECT _Title FROM Contents Z WHERE Z._Book = Y._Book AND Z._Chapter = Y._Chapter AND Z._Topic = '') AS _xChapterTitle,
_Topic,
_Title
FROM
Contents Y
JOIN Documents D ON _ID = _fkContents
WHERE D._Document CONTAINS 'geronimo'

Roy Lambert