The second method is dynamic sql with prepare statement from string.
It's better for the reason that the query is generated to retrieve data from
just one table (not an union which implies 3 tables).
The day your tables will be huge, i'm sure you will use the second method.
two crazy people can find a method because only one hand can't aplaude !
Mathias
Selon Sebastian <[EMAIL PROTECTED]>:
> Hi, your second method is probably a little too confusing (advanced) for
> me to understand.
> I used your first method which works fine.. thanks for the crazy stuff,
> somtimes you need two crazy people to come up with a solution ;)
>
> [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
> >
> >
> >
> >Selon Sebastian <[EMAIL PROTECTED]>:
> >
> >
> >
> >>Michael Stassen wrote:
> >>
> >>
> >>
> >>>Sebastian wrote:
> >>>
> >>>
> >>>
> >>>>i have a query with 3 union selects:
> >>>>
> >>>> SELECT id, 'news' AS type, .... FROM news
> >>>>
> >>>> UNION
> >>>> SELECT id, 'faq' AS type, .... FROM faq
> >>>>
> >>>> UNION
> >>>>
> >>>> SELECT id, 'forum' AS type, .... FROM forum
> >>>>
> >>>>which works just fine and selects everything from all 3 tables.. but
> >>>>say i want to make a condition to only select from either 'faq' ,
> >>>>'news' or 'forum' how can i do this?
> >>>>
> >>>>example, if a user visits a link suck as: page.php?cat=faq it will
> >>>>only select from 'faq' .. is this possible to do right in the query?
> >>>>when there is no ?cat= then all three selects run.
> >>>>
> >>>>makes sense? i am stuck on this for a few days already.
> >>>>thanks.
> >>>>
> >>>>
> >>>>
> >>>Why don't you do this in your app? If cat is set, issue the
> >>>appropriate single-table query, otherwise issue the union. Surely
> >>>that would be simpler than trying to build one multi-purpose query.
> >>>
> >>>Michael
> >>>
> >>>
> >>I was hoping i could do some crazy thing like WHERE type = 'faq' so i
> >>can do it all from one block of code.
> >>
> >>
> >>
> >>
> >>
> >>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
