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]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
