I don't think that solves his problem, since he wants to know AND, not
OR...
the closest thing I could get to was using select into... although it
seems like it might be possible with a left join, i just can't figure
out how.
one of the things that I thought of with select into was to select count
and the story name from the story,category, and storycategories join on
their respective ids, then used IN ('Classics','Childrens') and grouped
by story, yielding something like:
Peter Pan | 2
Alice in Wonderland | 2
Pokemon | 1
War and Peace | 1
Tale of Two Cities | 1
then selecting from that subset for count = 2;
if anyone can think of a way to do it with left joins, or a way to do it
without select into, i'd be very curious to see the solution :0
cheers,
Javier
-----Original Message-----
From: Rus [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 31, 2001 2:11 AM
To: [EMAIL PROTECTED]
Subject: Re: AND on a many to many table, with arbitrary ANDs
I think you could use 'WHERE catid IN'
For example, on PHP
$catlist="1,2,3,4,5";
... select ... where ... catid in ($catlist)
And of course you should use category id in web form instead category
name.
----- Original Message -----
From: Beasley, Julien <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, January 31, 2001 7:50 AM
Subject: AND on a many to many table, with arbitrary ANDs
> Hello everyone,
>
> I am in the process of building a search engine on a database. I have
two
> tables that have a many-to-many relationship: A story table and a
category
> table.
>
> Story Table:
> -----------
> id | story|
> -----------
> 1 | Alice in Wonderland
> 2 | Peter Pan
> 3 | Pokemon's adventure
> 4 | Tale of Two cities
> 5 | War and Peace
>
> ..etc..
>
>
>
> Category Table:
> --------------
> id | category|
> --------------
> 1 | Children
> 2 | Classics
> 3 | Tolstoy
> ..etc..
> `
>
>
> Storycategories Table:
> ----------------------
> storyid | catid |
> ---------------------
> 1 | 1
> 1 | 2
> 2 | 1
> 2 | 2
> 3 | 1
> 4 | 2
> 5 | 2
> 5 | 3
> ..etc..
>
>
> >From my (admittedly dim) understanding of SQL, this is how one is
supposed
> to organize a many-to-many relationship. So peter pan is listed as 1
> (childrens) and 2 (classics).
>
> I am writing a web interface to do searches for stories. In my web
> interface, you have something like this:
>
> ---------------------------------------------------------------
>
> []Classics []Childrens []Tolstoy []Fiction ...
>
> Search Categories:
> (x) match all categories
> ( ) match any category
>
> ---------------------------------------------------------------
>
> In order to build a query that matches ALL categories, I use aliasing.
To
do
> a search on stories that are Children AND Classics, I make two
instances
of
> each table as shown in the example below.
>
> select
> S.story
> from
> stories_tbl S,
> categories_tbl C1, storycategories_tbl O1,
> categories_tbl C2, storycategories_tbl O2
> where
> S.id = O1.storyid and O1.catid = C1.id and
> C1.category like "Children" and
> S.id = O2.storyid and O2.catid = C2.id and
> C2.category like "Classic"
> group by
> story
>
>
> This query works fine. My problem arises when I have many categories
to
> choose from. If I check many of the categories boxes, and select match
all
> categories, my program builds a query with a large amount of tables, I
get
> an error like this:
>
> Error 1116: Too many tables. MySQL can only use 32 tables in a join
>
> My guess is that there is a limit of 32 tables in a join, and that my
use
of
> aliasing here ends up violating the 32 table limit.
>
> Even worse is when I make a query that has a little less than 32
tables.
> This won't give an error, but will leave a process running on the
machine
> that consumes 100% cpu. After a while, this process will render the
database
> unusable, and no one can use the database until the process is killed!
>
>
> So to clarify, the query that ends up being built looks like this:
> select
> S.story
> from
> stories_tbl S,
> categories_tbl C1, storycategories_tbl O1,
> categories_tbl C2, storycategories_tbl O2
> ....
> categories_tbl Cn, storycategories_tbl On
>
> where
> S.id = O1.storyid and O1.catid = C1.id and
> C1.category like "Children" and
> S.id = O2.storyid and O2.catid = C2.id and
> C2.category like "Classic"
> ...
> S.id = On.storyid and On.catid = Cn.id and
> Cn.category like "xxxx"
> group by
> story
>
>
> This query fails when "n" is around 16.
>
> My question is, how can I build a query that will support an arbitrary
> number of ANDs on a many to many relationship? I'd like to be able to
do a
> search on stories that are "Children AND Classics AND Tolstoy AND....
> Category N" where N is fairly large (hundred or so)..
>
> Is there any way to do this? Am I using aliasing incorrectly?
>
> Those of you that have made it in reading this far, thank you :). I
> appreciate your attention and any assistance you might lend me in this
> matter.
>
> Julien Beasley
>
>
>
> ---------------------------------------------------------------------
> 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
---------------------------------------------------------------------
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
