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]
