Professional Web Applications Themes

subqueries in query of queries - Coldfusion Database Access

I've been looking at this for a while so I might be punch drunk. I have a query of queries on a mySQL sales database. I want to display columns across for the monthly sales values - client names on the Y axis, monthly sales on the Y axis... pretty common situation. I've been working on a solution which merges several SELECT statements (one per month where only the month I'm interested in has values and the other is 0 ) and then all unioned with UNION ALL and then, around all that, I have a summing statement, adding them ...

  1. #1

    Default subqueries in query of queries

    I've been looking at this for a while so I might be punch drunk.

    I have a query of queries on a mySQL sales database. I want to display
    columns across for the monthly sales values - client names on the Y axis,
    monthly sales on the Y axis... pretty common situation.

    I've been working on a solution which merges several
    SELECT
    statements (one per month where only the month I'm interested in has values
    and the other is 0 ) and then all unioned with
    UNION ALL
    and then, around all that, I have a summing statement, adding them all
    together.

    I'm trying to do this now in a cfm file and the query of queries is rebelling
    at the first
    (
    (when I'm about to get into my subquery).

    My questions are:
    1. can query of queries support subqueries (perhaps my syntax is wrong)
    2. anyone have a better way of displaying montly values on a summing database

    Any advice much appreciated!

    Helen


    helenmhudson Guest

  2. #2

    Default Re: subqueries in query of queries

    I don't think QofQ supports subqueries in the from clause which is what you need. An alternative approach would be to loop through your query and create a 2D array.
    Dan Bracuk Guest

  3. #3

    Default Re: subqueries in query of queries

    You could try this approach:

    First, split out the subquery into its own query:

    <cfquery name="mySubquery" dbtype="query">
    select myfields from myquery
    </cfquery>

    Then, store the results into a quoted list:

    <cfset MyList = QuotedValueList(mysubquery.myfields)>

    Then, use the resulting list in the WHERE clause of your main query (be sure
    to use the PreserveSingleQuotes function):

    <cfquery name="myfinalQuery" dbtype="query">
    Select allMyFields
    from myOtherQuery
    Where myfield NOT IN (#PreserveSingleQuotes(MyList)#)
    </cfquery>

    LL@Work Guest

Similar Threads

  1. Query of Queries
    By restlessmedia in forum Coldfusion Database Access
    Replies: 12
    Last Post: September 12th, 09:51 PM
  2. Need Help with Query of Queries
    By LyndonPatton in forum Coldfusion Database Access
    Replies: 6
    Last Post: April 18th, 09:02 PM
  3. 2 queries to 1 query
    By Samall in forum Macromedia ColdFusion
    Replies: 4
    Last Post: May 26th, 12:29 PM
  4. Query of Queries in 7.0
    By Funke in forum Macromedia ColdFusion
    Replies: 0
    Last Post: May 17th, 11:12 PM
  5. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 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