Best I can come up with is something like
2 words:
SELECT c1.id
FROM crossref c1, crossref c2
WHERE c1.word='word1'
AND c2.word='word2'
AND c1.id=c2.id
3 words:
SELECT c1.id
FROM crossref c1, crossref c2, crossref c3
WHERE c1.word='word1'
AND c2.word='word2'
AND c3.word='word3'
AND c1.id=c2.id
AND c1.id=c3.id
----- Original Message -----
From: "Jaime Teng" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, July 11, 2001 13:03
Subject: help me with "select" query
> Hi,
>
> I have a table:
> mysql> describe crossref;
> +-------+------------------+------+-----+
> | Field | Type | Null | Key |
> +-------+------------------+------+-----+
> | word | char(15) | | MUL |
> | id | int(10) unsigned | | MUL |
> +-------+------------------+------+-----+
>
> +---------------+------+
> | word | id |
> +---------------+------+
> | tintin | 1604 |
> | registers | 1604 |
> | 9207844 | 1604 |
> | superman | 1621 |
> | registers | 1621 |
> | 4479462 | 1621 |
> | angelo | 1622 |
> | registers | 1622 |
> | 6330586 | 1622 |
> | pforshag | 1662 |
> | registers | 1662 |
> | 6344168 | 1662 |
> | tintin | 1689 |
> | authenticates | 1689 |
> +---------------+------+
>
> How do I combine these:
> SELECT id FROM crossref WHERE word = 'word1';
> SELECT id FROM crossref WHERE word = 'word2';
> SELECT id FROM crossref WHERE word = 'word3';
> such that I get the intersection of each sets?
>
> SELECT id FROM crossref WHERE word = 'word1' or
> word = 'word2' or word = 'word3';
> is not correct because this will list down as well
> other numbers that does not contain all three words.
>
> example:
> search "tintin"
> result = 1604, 1689
>
> search "registers"
> result = 1604, 1621, 1622, 1662
>
> search "registers", "tintin"
> result = 1604
>
>
>
> thanks
> jaime
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
