Hello all,
I'm trying to form a specific query, but not sure if that's possible at
all and if it is, how it should look like. I'd really appreciate your
help :-)
I have 2 tables (simplest case, but once I know this, I can work out the
rest):
CREATE TABLE TableA(
UID INTEGER PRIMARY KEY,
Text,
PARENTTABLEID,
RECORDID)
CREATE TABLE TableMain(
UID INTEGER PRIMARY KEY,
Text)
Where TableA's RECORDID is in fact the UID of TableMain and
PARENTTABLEID can be filled with TableA's UID to show hierarchical
relationship, so they can go like this:
TableMain
|--- TableA
|--- TableA
Ok, I need to search through the Text field, so for simple tables I had
this (filing with appropriate data of course using sqlite3_mprintf()
function):
SELECT * FROM TableMain WHERE Text LIKE '%%%q%%'
and I had UNION if I needed to search on more than one field. So far so
good, but now I have this hierarchy of TableA's, that also need to be
searched through, and I'd like to have one SQL statement that does it,
if possible.
I'd like to know if I can do something like this (incorrect, but to show
the idea):
SELECT * FROM TableMain WHERE Text LIKE 'x' UNION SELECT * FROM TableA
WHERE TableA.RECORDID=TableMain.UID AND Text LIKE 'x'
??? (and how could I go through recursive TableA's?)
Thank you for reading this :-)
I'd appreciate any comments, and I'd like to know if this is possible at
all.
Regards,
Dennis
