Ed Williams wrote:
>
> I am developing a search engine that searches through a table of documents
> matching keywords in the title, this is straitforward ;) but i also need to search
> through
> a second database which has keywords matching a subject code like so
>
> +-------------+------------------------------+----+
> | subjectCode | keywords | ID |
> +-------------+------------------------------+----+
> | 4809 | Law Scotland | 9 |
> | 4809 | Statutes Scotland | 10 |
> | 4810 | Legislation Northern Ireland | 11 |
> | 4810 | Acts Northern Ireland | 12 |
> | 4810 | Northern Ireland legislation | 13 |
> +-------------+------------------------------+----+
>
> The subject code then matches a document code in another table like so.
>
> +-------------+-------------+----+
> | documentRef | subjectCode | ID |
> +-------------+-------------+----+
> | 004943 | 8056 | 1 |
> | 087826 | 7031 | 2 |
> | 050581 | 7016 | 3 |
> | 098604 | 7016 | 4 |
> | 097572 | 7016 | 5 |
> +-------------+-------------+----+
>
> So once i have the documentCode from the document database and the document Code
> from above tables
> i need to merge them into one table removing duplicates , then i can save it into a
> temporary table/file for
> the search session.
>
> I have tried many ways to do this as quickly as possible but it always seems to take
> forever and is driving me mad.
> is there any way to get the document code for the subject from a keyword with 1 sql
> statement maybe using joins.
> Sorry if this is bit of a long one but im desprate ;)
>
> Cheer's Ed.
Hi Ed
I'm not sure I understand where your problem is.
But I think you search for something like this:
SELECT
d.documentRef
d.document
FROM
documnets AS d
,keywords AS k
,subjects AS s
WHERE
d.documentRef = s.documentRef
AND s.subjectCode = k.subjectCode
AND k.keywords = 'Statuetes Scotland'
GROUP BY
d.documentRef
Tschau
Christian
