2 queries to 1 query - Macromedia ColdFusion

[Samall]

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

[Kiriran]

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

[Samall]

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]

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]

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