did'n arrive. re-submitted -- sorry
Selon [EMAIL PROTECTED]:
> Hi,
> If we forget the first method which i mis-adviced, i can give a third which
> is
> generic.
>
> suppose that you have an indexed "type" column on each table (what i did).
> You
> can work with 3 variables. If they are different, you query for a join, if
> they
> are equal, you transform the join to a simple query.
> The only condition is to add a where clause a the column "type" which will
> retreive empty set for the non selected conditions.
>
> Example :!
>
> set @cat1:='news'; set @cat2:='faq'; set @cat3:='forum';
>
> mysql> select id,@cat1 as selected, type from news where [EMAIL PROTECTED]
> -> union select id,@cat2 as selected, type from faq where [EMAIL
> PROTECTED]
> -> union select id,@cat3 as selected, type from forum where
> [EMAIL PROTECTED];
> +------+----------+-------+
> | id | selected | type |
> +------+----------+-------+
> | 1 | news | news |
> | 2 | faq | faq |
> | 3 | forum | forum |
> +------+----------+-------+
> 3 rows in set (0.00 sec)
>
>
> When you have only one value, the same query gives :
> ****************************************************
>
>
> mysql> set @cat1='news'; set @cat2='news'; set @cat3='news';
> Query OK, 0 rows affected (0.00 sec)
>
> here the 3 variables are the same, so 2 queries will find an empty set.
>
> mysql> select id,@cat1 as selected, type from news where [EMAIL PROTECTED]
> -> union select id,@cat2 as selected, type from faq where [EMAIL
> PROTECTED]
> -> union select id,@cat3 as selected, type from forum where
> [EMAIL PROTECTED];
> +------+----------+------+
> | id | selected | type |
> +------+----------+------+
> | 1 | news | news |
> +------+----------+------+
> 1 row in set (0.00 sec)
>
> performance will not be affected since the index will be used for non used
> tables.
>
> Hope that helps :o)
>
> Mathias
>
>
>
> Selon Sebastian <[EMAIL PROTECTED]>:
>
> > Michael Stassen wrote:
> >
> > > [EMAIL PROTECTED] wrote:
> > >
> > >> Hi Sebastian;
> > >> There is always crazy things somewhere.
> > >> I'll give you two methods for that :
> > >>
> > >> mysql> select id,'news' as selected, type from news
> > >> -> union select id,'faq' as selected, type from faq
> > >> -> union select id,'forum' as selected, type from forum;
> > >> +------+----------+-------+
> > >> | id | selected | type |
> > >> +------+----------+-------+
> > >> | 1 | news | news |
> > >> | 2 | faq | faq |
> > >> | 3 | forum | forum |
> > >> +------+----------+-------+
> > >> 3 rows in set (0.00 sec)
> > >>
> > >>
> > >> FIRST CRAZY METHOD :
> > >> *************************
> > >> mysql> set @cat='news';
> > >> Query OK, 0 rows affected (0.00 sec)
> > >>
> > >> mysql> select * from (
> > >> -> select id,'news' as selected, type from news
> > >> -> union select id,'faq' as selected, type from faq
> > >> -> union select id,'forum' as selected, type from forum
> > >> -> ) Temp
> > >> -> where [EMAIL PROTECTED];
> > >> +------+----------+------+
> > >> | id | selected | type |
> > >> +------+----------+------+
> > >> | 1 | news | news |
> > >> +------+----------+------+
> > >> 1 row in set (0.00 sec)
> > >>
> > >>
> > >> SECOND CRAZY METHOD (I prefer):
> > >> *************************
> > >>
> > >> set @cat := 'news';
> > >> set @sql:=concat('select id,','''',@cat,'''',' as selected from ',@cat);
> > >> select @sql;
> > >> prepare stmt from @sql ;
> > >> execute stmt;
> > >>
> > >> +------+----------+
> > >> | id | selected |
> > >> +------+----------+
> > >> | 1 | news |
> > >> +------+----------+
> > >> 1 row in set (0.00 sec)
> > >>
> > >> deallocate prepare stmt;
> > >>
> > >>
> > >> ********* another click with ?cat=faq
> > >>
> > >> set @cat := 'faq';
> > >> set @sql:=concat('select id,','''',@cat,'''',' as selected from ',@cat);
> > >> select @sql;
> > >> prepare stmt from @sql ;
> > >> execute stmt;
> > >>
> > >> mysql> execute stmt;
> > >> +------+----------+
> > >> | id | selected |
> > >> +------+----------+
> > >> | 2 | faq |
> > >> +------+----------+
> > >> 1 row in set (0.00 sec)
> > >>
> > >> deallocate prepare stmt;
> > >>
> > >> OTHER CRAZY METHODS - coming emails :o)
> > >> --------------------
> > >>
> > >> A+
> > >> Mathias
> > >
> > >
> > > The first method is horribly inefficient (and requires mysql 4.1+).
> > > It reads all 3 tables, unions the resulting rows, checks for (and
> > > removes) duplicate rows, then finally throws away roughly 2/3 of the
> > > results (the rows from the 2 unwanted tables. Compare that to the
> > > simple query which only addresses the 1 desired table. Mathias is
> > > aware of this, which is why he gives the second method. It creates
> > > the simple, one-table query using the value of $cat to choose which
> > > table.
> > >
> > > The big problem here is that neither of these methods actually do what
> > > you asked for. That is, neither works if $cat is not set. With both
> > > methods, you will get no rows unless $cat is set. In fact, the second
> > > method will give a syntax eror, as there will be no table name in the
> > > FROM clause.
> > >
> > > Now, I never said this couldn't be done in SQL. Assuming $cat is
> > > already set, the statement in $sql below should do what you asked for:
> > >
> > > $sql = "SELECT id, 'news' AS type, .... FROM news
> > > WHERE ($cat = '' OR $cat = 'news')
> > > UNION
> > > SELECT id, 'faq' AS type, .... FROM faq
> > > WHERE ($cat = '' OR $cat = 'faq')
> > > UNION
> > > SELECT id, 'forum' AS type, .... FROM forum
> > > WHERE ($cat = '' OR $cat = 'forum')"
> > >
> > > MySQL's optimizer should be smart enough to notice that if $cat is
> > > set, 2 of the 3 unioned queries will have impossible WHERE clauses, so
> > > those queries will be skipped, making this relatively efficient.
> > >
> > > Even though this works, though, I think it's a bit of a hack. It's
> > > clever SQL which allows two separate queries to masquerade as one,
> > > based on the value of $cat. I really think the simple, separate query
> > > solution I sent in my previous message
> > >
> > > if ($cat == 'news' or $cat == 'faq' or $cat == 'forum')
> > > {
> > > $sql = "SELECT id, '$cat' AS type, ... FROM $cat";
> > > }
> > > else
> > > {
> > > $sql = " SELECT id, 'news' AS type, .... FROM news
> > > UNION
> > > SELECT id, 'faq' AS type, .... FROM faq
> > > UNION
> > > SELECT id, 'forum' AS type, .... FROM forum";
> > > }
> > >
> > > is better, as it is clearer what we are doing (one query if we know
> > > the table, three queries unioned if we don't), so it should be easier
> > > to understand and maintain down the road (by you or the next guy).
> > >
> > > Michael
> >
> > you just gave me an idea.. maybe this is better:
> >
> > SET @cat = '$cat';
> >
> > SELECT id, 'news' AS type,
> > WHERE [expression...] AND (@cat = 'news' OR @cat = '')
> >
> > UNION
> > SELECT id, 'faq' AS type,
> > WHERE [expression...] AND (@cat = 'faq' OR @cat = '')
> >
> > UNION
> > [SELECT ........]
> >
> > works regardless if $cat is set or not.
> >
> >
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
