The reason is, I need to select different subsets of that View in
multiple places in my code. It is much nicer to have a short select
statement than having a 10-line-long one. And I presume it's faster
too, not to have to send all this text each time over JDBC.
Is there a way?

On Mar 13, 6:03 am, "Captain Paralytic" <paul_laut...@yahoo.com>
wrote:
> On 13 Mar, 00:31, "Paul" <paul_l...@hotmail.com> wrote:
>
>
>
> > Hi,
> > This works great - thank you - but only if I run it manually (in a
> > query window).
> > If I try to save it as a View, it fails, saying
> > "View's SELECT contains a subquery in the FROM clause".
> > Apparently, according tohttp://dev.mysql.com/doc/refman/5.1/en/create-view.html
> > this is not allowed.
>
> > Therefore, do you know how to either:
> > 1. Do the same thing without using a subquery
> > OR
> > 2. Store it inside my MySQL DB as something other than a View? I'm new
> > to this, but as far as I understand, Procedures don't return values
> > and UDFs return singular values and not record sets. So how can I
> > store it so I don't need to send this each time manually from my
> > application?
> > Thank you...
>
> > On Mar 12, 3:11 pm, "strawberry" <zac.ca...@gmail.com> wrote:
>
> > > On 12 Mar, 16:24, "Paul" <paul_l...@hotmail.com> wrote:
>
> > > > anyone?..
>
> > > Erm...
>
> > > SELECT UserID,
> > > IFNULL(Stage1, 0) AS Stage1, IFNULL(Stage2, 0) AS Stage2,
> > > IFNULL(Stage3, 0) AS Stage3, IFNULL(Stage4, 0) AS Stage4
> > > FROM Users LEFT OUTER JOIN
> > > (SELECT
> > > Actions.PerformerID,
> > > COUNT(Actions.ActionID) AS ActionsTotal,
> > > COUNT((CASE WHEN (ActionStatus.ActionStatusName = 'Stage1') THEN
> > > Actions.ActionID END)) AS Stage1,
> > > COUNT((CASE WHEN (ActionStatus.ActionStatusName = 'Stage2') THEN
> > > Actions.ActionID END)) AS Stage2,
> > > COUNT((CASE WHEN (ActionStatus.ActionStatusName = 'Stage3') THEN
> > > Actions.ActionID END)) AS Stage3,
> > > COUNT((CASE WHEN (ActionStatus.ActionStatusName = 'Stage4') THEN
> > > Actions.ActionID END)) AS Stage4
> > > FROM Actions, ActionStatus
> > > WHERE
> > > ActionStatus.ActionStatusID = Actions.Status
> > > GROUP BY Actions.PerformerID)Action_Stats
> > > ON Users.UserID = Action_Stats.PerformerID;- Hide quoted text -
>
> > - Show quoted text -
>
> If it's coming from an application, what's the problem with sending it
> each time?