Professional Web Applications Themes

2 queries to 1 query - Macromedia ColdFusion

Hello, Can somebody help me to combine these two queries into one query. <cfquery datasource="#DATAS#" name="getMainNav"> SELECT * FROM navigatie2 WHERE taalid = #taalid# AND navigatieid1 = #navigatieid1# AND active = 1 ORDER BY orderID </cfquery> <cfif #url.navigatieID2# NEQ ''> <cfoutput query="getMainNav"> <cfquery datasource="#DATAS#" name="getSubNav"> SELECT * FROM navigatie3 WHERE navigatieID2 = #url.navigatieID2# AND taalid = #taalid# AND active = 1 ORDER BY orderID </cfquery> </cfoutput> </cfif> Thanks! Samall...

  1. #1

    Default 2 queries to 1 query

    Hello,
    Can somebody help me to combine these two queries into one query.

    <cfquery datasource="#DATAS#" name="getMainNav">
    SELECT *
    FROM navigatie2
    WHERE taalid = #taalid# AND
    navigatieid1 = #navigatieid1# AND
    active = 1
    ORDER BY orderID
    </cfquery>
    <cfif #url.navigatieID2# NEQ ''>
    <cfoutput query="getMainNav">
    <cfquery datasource="#DATAS#" name="getSubNav">
    SELECT *
    FROM navigatie3
    WHERE navigatieID2 = #url.navigatieID2# AND
    taalid = #taalid# AND
    active = 1
    ORDER BY orderID
    </cfquery>
    </cfoutput>
    </cfif>

    Thanks!
    Samall

    Samall Guest

  2. #2

    Default Re: 2 queries to 1 query

    i dont know which columns you want in your new query but you would have to
    build a new query like this
    <cfset myQuery = QueryNew("column1, column2, column3", "VarChar, Integer,
    VarChar") />
    <cfset addrow = QueryAddRow(myQuery) />
    <cfset temp = QuerySetCell(myQuery, "column1", #value#) />
    <cfset temp = QuerySetCell(myQuery, "column2", #value2#) />
    <cfset temp = QuerySetCell(myQuery, "column3", #value3#) />

    or you could build your query diffrent and use inner join if possible

    Kiriran Guest

  3. #3

    Default Re: 2 queries to 1 query

    What I'm trying to do is create a navigation for a website.
    I want to show the NAVIGATIEID2 items and when you click on a NAVIGATIEID2
    item, I want to display Iif they exsists) the NAVIGATIEID3 items.


    <cfquery datasource="#DATAS#" name="getMainNav">
    SELECT navigatieid2, name, active, taalid, navigatieid1
    FROM navigatie2
    WHERE taalid = #taalid# AND
    navigatieid1 = #navigatieid1# AND
    active = 1
    ORDER BY orderID
    </cfquery>

    <cfif #url.navigatieID2# NEQ ''>
    <cfoutput query="getMainNav">
    <cfquery datasource="#DATAS#" name="getSubNav">
    SELECT navigatieid2, name, active, taalid, navigatieid3
    FROM navigatie3
    WHERE navigatieID2 = #url.navigatieID2# AND
    taalid = #taalid# AND
    active = 1
    ORDER BY orderID
    </cfquery>
    </cfoutput>
    </cfif>

    Samall Guest

  4. #4

    Default Re: 2 queries to 1 query

    It's hard to tell the relationships since not all of your variables are
    fully scoped and you're using SELECT * in your query. Can you provide a
    brief description of the relationships and what information you're trying to
    pull?

    "Samall" <webforumsusermacromedia.com> wrote in message
    news:d73v97$hl9$1forums.macromedia.com...
    > Hello,
    > Can somebody help me to combine these two queries into one query.
    >
    > <cfquery datasource="#DATAS#" name="getMainNav">
    > SELECT *
    > FROM navigatie2
    > WHERE taalid = #taalid# AND
    > navigatieid1 = #navigatieid1# AND
    > active = 1
    > ORDER BY orderID
    > </cfquery>
    > <cfif #url.navigatieID2# NEQ ''>
    > <cfoutput query="getMainNav">
    > <cfquery datasource="#DATAS#" name="getSubNav">
    > SELECT *
    > FROM navigatie3
    > WHERE navigatieID2 = #url.navigatieID2# AND
    > taalid = #taalid# AND
    > active = 1
    > ORDER BY orderID
    > </cfquery>
    > </cfoutput>
    > </cfif>
    >
    > Thanks!
    > Samall
    >

    _jt Guest

  5. #5

    Default Re: 2 queries to 1 query

    Well, you probably don't need the the CFOUTPUT loop to accomplish that, but
    why do you need the information in a single query?

    The following should give you a single query containing all of the
    NAVIGATIEID2 items and the matching sub items (if any). Because it's in a
    single query, some of the NAVIGATIEID2 may be duplicated, so you would need
    to GROUP the output.




    SELECT n2.navigatieid2 AS ItemID, n2.Name AS ItemName,
    n2.active AS ItemActive, n2.taalid AS ItemTaalid,
    n2.navigatieid1 AS ItemParentID,
    n3.navigatieid2 AS SubItemID, n3.Name AS SubItemName,
    n3.active AS SubItemActive, n3.taalid AS SubItemTaalid,
    n3.navigatieid2 AS SubItemParentID
    FROM navigatie2 n2 LEFT JOIN navigatie3 n3 ON
    ( n2.navigatieid2 = n3.navigatieID2 AND
    n2.taalid = n3.taalid AND
    n2.active = n3.active
    )
    WHERE n2.navigatieid1 = #navigatieid1# AND
    n2.taalid = #taalid# AND
    n2.active = 1


    Also, it seems like NAVIGATIEID2 and NAVIGATIEID3 contain the same type of
    item and have basically the same structure. If so, why are there separate
    tables? Couldn't you use a single table with some type of self-referencing
    parentID column?

    "Samall" <webforumsusermacromedia.com> wrote in message
    news:d743ip$pg6$1forums.macromedia.com...
    > What I'm trying to do is create a navigation for a website.
    > I want to show the NAVIGATIEID2 items and when you click on a
    NAVIGATIEID2
    > item, I want to display Iif they exsists) the NAVIGATIEID3 items.
    >
    >
    > <cfquery datasource="#DATAS#" name="getMainNav">
    > SELECT navigatieid2, name, active, taalid, navigatieid1
    > FROM navigatie2
    > WHERE taalid = #taalid# AND
    > navigatieid1 = #navigatieid1# AND
    > active = 1
    > ORDER BY orderID
    > </cfquery>
    >
    > <cfif #url.navigatieID2# NEQ ''>
    > <cfoutput query="getMainNav">
    > <cfquery datasource="#DATAS#" name="getSubNav">
    > SELECT navigatieid2, name, active, taalid, navigatieid3
    > FROM navigatie3
    > WHERE navigatieID2 = #url.navigatieID2# AND
    > taalid = #taalid# AND
    > active = 1
    > ORDER BY orderID
    > </cfquery>
    > </cfoutput>
    > </cfif>
    >

    _jt Guest

Similar Threads

  1. Query of Queries
    By restlessmedia in forum Coldfusion Database Access
    Replies: 12
    Last Post: September 12th, 09:51 PM
  2. query of queries with avg()
    By gogl in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: July 6th, 09:50 PM
  3. Query of Queries in 7.0
    By Funke in forum Macromedia ColdFusion
    Replies: 0
    Last Post: May 17th, 11:12 PM
  4. 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
  5. Query of Queries?
    By artists_envy in forum Coldfusion Database Access
    Replies: 3
    Last Post: February 25th, 04:31 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