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 "" 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
RE: AND on a many to many table, with arbitrary ANDs
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 ""
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
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 "" 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
Re: AND on a many to many table, with arbitrary ANDs
if my understanding is correct just have two joins using ors for the categories :-) On Tue, 30 Jan 2001, Beasley, Julien wrote: 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 "" 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 -- -Spinlock EmpireQuest Creator http://www.empirequest.com - 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
Re: AND on a many to many table, with arbitrary ANDs
Julien What you're trying to do isn't that uncommon, and I've met the same problems in two major systems that I've built over the last year. My solution was to use SQL up to a point (i.e. limit the complexity of the query) to protect the performance of the server. So, for example, you can read every record from the StoryCategories table into either a Perl script (if you want fast development) or a C program (if you want fast CGI performance) and examine then accept/reject each as you meet it. It takes a bit more programming skill, but the server will survive far more simultaneous enquiries that way than it will with a single complex SQL query. Hope this helps some. The Hooker -- "I've had a perfectly wonderful evening, but this wasn't it" 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 "" 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
