List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:June 11 1999 1:26pm
Subject:Re: Please can anybody help me ...
View as plain text  
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

Thread
Please can anybody help me ...Ed Williams11 Jun
  • Re: Please can anybody help me ...Christian Mack11 Jun
    • Re: Please can anybody help me ...jonathan michaels12 Jun
  • Compiling mysql under HPUX 11Clay Olbon11 Jun
Re: Please can anybody help me ...Ruprecht Helms11 Jun
Re: Compiling mysql under HPUX 11Ralf Kraudelt11 Jun