On 11-Jul-01 Jaime Teng wrote:
> 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
>
>
select id,count(*) as cnt
from crossref where word in ('tintin', 'registers', ...)
group by id having cnt > 1 order by cnt
if cnt == # of words; you have an intersection (there can be > 1)
if cnt < # of words you've got the best match.
Regards.
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
---------------------------------------------------------------------
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
