Re: Need help with SQL
This sounds like a prime case for using the relational calculus instead of
the relational algebra, as advocated by C J Date in 'Relational Database:
Writings 1989-1991', Chapter 9, 'Relational Calculus as an Aid to Effective
Query Formulation' (Addison-Wesley, 1992, ISBN 0-201-54303-6). I will use
at least some of the notation from that book. SQL is more closely based on
the algebra than the calculus, but the calculus and the algebra are equally
powerful and formally equivalent to each other.
You have two criteria, C1 and C2. C1 uses FORALL because you want only the
documents where all the relevant terms are listed. C2 uses EXISTS because
you want the documents which contain any of the relevant terms. C2 is much
easier to deal with, and your solution will certainly work.
For the purposes of discussion, assume that there is a table of interesting
index terms called TERMS (with column Term). Also, assume that the range
variables (see the reference) are TX for Terms, DX for Docs, IX for Indx.
>First query condition:
> each single word of a list of words is attached to
> the desired document
C1-A:
select document IDs such that for all terms in the Terms table
there exists an entry in the Indx table where the Indx.Term =
Terms.Term
C1-B:
DX.DocID WHERE FORALL TX
(EXISTS IX (IX.Term = TX.Term AND IX.DocID = DX.DocID))
>Second query condition:
> at least one word of a list of words is attached to
> the desired document
C2-A:
select document IDs such that there exists a term in the Terms
table for which there exists an entry in the Indx table where the
Indx.Term = Terms.Term
C2-B:
DX.DocID WHERE EXISTS TX
(EXISTS IX (IX.Term = TX.Term AND IX.DocID = DX.DocID))
There is a nice symmetry between C1-B and C2-B in this notation.
There is an important identity which allows queries using FORALL to be
rewritten (obscurely) in SQL using EXISTS.
FORALL x (p) <=> NOT (EXISTS x (NOT(p)))
Hence, we can try to rewrite the C1-B systematically using this:
C1-C:
DX.DocID WHERE NOT (EXISTS TX WHERE
(NOT (EXISTS IX (IX.Term = TX.Term AND IX.DocID = DX.DocID))))
From here, it is mainly a mechanical process to translate the queries into SQL.
C1-SQL:
SELECT DX.DocID
FROM Docs DX
WHERE NOT (EXISTS (SELECT *
FROM Terms TX
WHERE (NOT (EXISTS (SELECT *
FROM Indx IX
WHERE (IX.Term = TX.Term AND
IX.DocID = DX.DocID))))))
C2-SQL:
SELECT DX.DocID
FROM Docs DX
WHERE EXISTS (SELECT *
FROM Terms TX
WHERE EXISTS (SELECT * FROM Indx IX
WHERE (IX.Term = TX.Term
AND IX.DocID = DX.DocID
)))
Since I wouldn't believe this without testing, I assembled the script below
to test the above queries. This is not directly interpretable by ISQL or
DB-Access, but if you have my SQLCMD program, you should be able to run it
directly. It shouldn't take very much effort to see that the two queries
(C1-SQL and C2-SQL) are in a file c1c2.sql, and that this file is included
at selected points.
Much to my delight, when I ran the first version of the test with just
"Test 1" and "Test 2" in it, it all worked. I then extended it to add
Tests 3-5, and Test 5 gave the 'wrong' result. I added the extra debugging
to find out what was wrong and eventually noticed that I was inserting a
second row with value "eeee" into the Terms table, instead of the intended
value "bbbb". I was getting the right result because I was mistaken about
the data I was testing with.
Exercises to remove the table Terms and replace it with an IN list are left
for the reader! So too is remving the redundant parentheses, a much easier
task, I might add.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
---------------------------------------------------------------------------
create temp table docs (docid integer not null);
insert into docs values(1);
insert into docs values(2);
insert into docs values(3);
create temp table indx (term char(4) not null, docid integer not null);
insert into indx values ("aaaa", 1);
insert into indx values ("dddd", 1);
insert into indx values ("eeee", 1);
insert into indx values ("ffff", 1);
insert into indx values ("hhhh", 1);
insert into indx values ("cccc", 2);
insert into indx values ("dddd", 2);
insert into indx values ("ffff", 2);
insert into indx values ("gggg", 2);
insert into indx values ("aaaa", 3);
insert into indx values ("bbbb", 3);
insert into indx values ("eeee", 3);
create temp table terms (term char(4) not null);
insert into terms values("dddd");
insert into terms values("ffff");
echo "Test 1: (dddd, ffff)";
select * from terms;echo "C1-SQL should select 1, 2";
echo "C2-SQL should select 1, 2";
input c1c2.sql;
insert into terms values("hhhh");
echo "Test-2: (dddd, ffff, hhhh)";
select * from terms;echo "C1-SQL should select 1";
echo "C2-SQL should select 1, 2";
input c1c2.sql;
insert into terms values("bbbb");
echo "Test-3: (dddd, ffff, hhhh, bbbb)";
select * from terms;echo "C1-SQL should select nothing";
echo "C2-SQL should select 1, 2, 3";
input c1c2.sql;
delete from terms;
insert into terms values("aaaa");
insert into terms values("eeee");
echo "Test-4: (aaaa, eeee)";
select * from terms;echo "C1-SQL should select 1, 3";
echo "C2-SQL should select 1, 3";
input c1c2.sql;
insert into terms values("bbbb");
echo "Test-5: (aaaa, bbbb, eeee)";
select * from terms;echo "C1-SQL should select 3";
echo "C2-SQL should select 1, 3";
input c1c2.sql;
drop table terms;
drop table docs;
drop table indx;---------------------------------------------------------------------------
>From: kieni@dfki.uni-kl.de (Thomas Kieninger)
>Date: 18 Jul 1995 09:15:38 GMT
>X-Informix-List-Id: <news.15566>
>
>Hello out there,
>
>could someone of you help me to formulate a SQL query?
>I will just tell you my problem:
>
>Assume two tables.
>
>a) Table DOCS with document descriptions:
>
>DOCS: DOC_ID | other info (of no interest here)
> ====================
> 1 | ...
> 2 | ...
> 3 | ...
> ... | ...
>
>b) Table INDX with indexterms of documents:
>
>INDX: TERM | DOC_ID
> ================
> aaaa | 1