Professional Web Applications Themes

Need help combining two queries - MySQL

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 ...

  1. #1

    Default 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

    Paul Guest

  2. #2

    Default Re: Need help combining two queries

    anyone?..

    On Mar 11, 5:45 pm, "Paul" <com> wrote: 


    Paul Guest

  3. #3

    Default Re: Need help combining two queries

    On 12 Mar, 16:24, "Paul" <com> wrote: 

    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;

    strawberry Guest

  4. #4

    Default 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" <com> wrote: 
    >
    > 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;[/ref]


    Paul Guest

  5. #5

    Default Re: Need help combining two queries

    On 13 Mar, 00:31, "Paul" <com> wrote: 
    > [/ref]


    >
    > - Show quoted text -[/ref]

    If it's coming from an application, what's the problem with sending it
    each time?

    Captain Guest

  6. #6

    Default 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" <com>
    wrote: 


    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > If it's coming from an application, what's the problem with sending it
    > each time?[/ref]


    Paul Guest

  7. #7

    Default Re: Need help combining two queries

    Paul wrote: 
    >> If it's coming from an application, what's the problem with sending it
    >> each time?[/ref]
    >
    >
    > 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?
    >[/ref]

    (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.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Query of Queries - Combining Two Datasources
    By storserver in forum Coldfusion Database Access
    Replies: 11
    Last Post: October 19th, 08:30 PM
  2. Combining two queries (searchterm, daterange)
    By RayBan12 in forum Macromedia ColdFusion
    Replies: 3
    Last Post: August 23rd, 02:01 PM
  3. Query of Queries Combining Results
    By wein in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: July 12th, 12:41 PM
  4. Combining Verity with Query of Queries
    By Kletian in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: April 1st, 06:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139