Need help combining two queries
This is a discussion on Need help combining two queries within the MySQL Database forums, part of the Database Forums category; Hi, Apologies for a simple question - I am very new to DB programming in general and MySQL in particular. I'...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
03-11-2007
|
|||
|
|||
Need help combining two queries
Hi,
Apologies for a simple question - I am very new to DB programming in general and MySQL in particular. I'm using MySQL 5.1 in case that matters. I need to efficiently combine the following two queries into one. The objective is to show the number of each type of actions taken by each user - and if none of a given type were taken, show 0. The problem is that not every User ever took an Action and thus might not be listed in Actions table as a Performer. I managed to get it down to two queries, but fail to combine them. What would be the most efficient way to do that? As an aside, if you think anything else in the query is bad, please say so - I'm only learning. Thank you! ==== Query #1: 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; I saved that query as a View named Action_Stats. I then do Query #2: 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 Action_Stats ON Users.UserID = Action_Stats.PerformerID 
|
|
03-12-2007
|
|||
|
|||
|
Re: Need help combining two queries
anyone?..
On Mar 11, 5:45 pm, "Paul" <paul_l...@hotmail.com> wrote: > Hi, > Apologies for a simple question - I am very new to DB programming in > general and MySQL in particular. > I'm using MySQL 5.1 in case that matters. > I need to efficiently combine the following two queries into one. > > The objective is to show the number of each type of actions taken by > each user - and if none of a given type were taken, show 0. The > problem is that not every User ever took an Action and thus might not > be listed in Actions table as a Performer. > > I managed to get it down to two queries, but fail to combine them. > What would be the most efficient way to do that? > As an aside, if you think anything else in the query is bad, please > say so - I'm only learning. > > Thank you! > ==== > Query #1: > 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; > > I saved that query as a View named Action_Stats. I then do > Query #2: > 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 Action_Stats > ON Users.UserID = Action_Stats.PerformerID
|
|
03-12-2007
|
|||
|
|||
|
Re: Need help combining two queries
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;
|
|
03-13-2007
|
|||
|
|||
|
Re: Need help combining two queries
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 to http://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;
|
|
03-13-2007
|
|||
|
|||
|
Re: Need help combining two queries
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?
|
|
03-13-2007
|
|||
|
|||
|
Re: Need help combining two queries
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?
|
|
03-14-2007
|
|||
|
|||
|
Re: Need help combining two queries
Paul wrote:
> 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? > > > 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? > (Top posting fixed) The difference between sending 50 bytes and 500 bytes across the connection can be measured in microseconds. If you have performance problems, there are a lot of other places you should look first. P.S. Please don't top post. Thanks. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ==================
|
Linear Mode
