need help with complicated query/output

Forum|Forum|19 years ago
November 24, 2006
8 replies
589 views

Hi everyone,

Still very new to CF I am having a bit of a hardtime with this particular project. there are 2 queries. (the queries CAN't be merged for whatever reason) the first one displays 2 columns, a QTY column and a Identification number.
the second query returns the same identification number and a short description.
what I need to do is have a table on a webpage that lists:
QTY Identification Short description

now the tricky part is that the query returns the first query in order of QTY and the second query has no sorting.

How would I go abouts doing this?

The data is not static, it changes now and again.

Thank you very much

Luc

This topic has been closed for replies.

A

Anonymous

I got it working.

Thanks to all of your help I managed to get it going, I had been trying to find a solution to this and the SQL guy I work with was bashing his head trying to get it done within the query and now we got it going with 5 lines of code.

Thank you very much.

Luc

N

Newsgroup_User

Inspiring

Using QofQ just produces a new record set that you then use like any
normal record set.

<cfquery ....name="combinedStuff" dbtype="query">
SELECT QTY, queryOne.ID AS ID, SHORTDESCRIPTION
FROM QueryOne, QueryTwo
WHERE queryOne.id = queryTwo.id
</cfquery>

<cfoutput query="combinedStuff">
#qty# #ID# #ShortDescription#<br/>
</cfoutput>

PS. I added the queryOne.ID to the select clause, since both record
sets have an ID field, you have to declare which one you want selected.

A

Anonymous

Thanks Dan,

so the first big query will give me QTY and ID, the second query gives me ID and ShortDescription (lets call it that so I don't get confused with Desc) so those 2 queries run and I get the output into ram. now I run the query within a query with similar to the code I put in my previous post, how do I grab all 3 fields that I want? qty, id, shortdescription and sort using QTY Descending?
soo close :D

Luc

D

Dan_Bracuk

Inspiring

close.

desc is a keyword, it's short for descending.

A

Anonymous

Hi Guys,

Thanks for all the info,

so if my main Queries (the 2 big ones) are called Query1 and Query 2 I would do:
<cfquery name="queryjoin" dbtype="query">
SELECT QTY, ID, DESC
FROM Query1, Query2
WHERE query1.id = query2.id
</cfquery>

Then I can do the normal output and put qty in one column, id in the another and short desc in the third?

Thank you.

Have a great weekend.

N

Newsgroup_User

Inspiring

Its just like writing a SQL join if you where joining two tables in a
single database. In a QofQ the tables are the existing record sets.

<cfquery .... dbtype="query">
SELECT QTY, ID, DESC
FROM QueryOne, QueryTwo
WHERE queryOne.id = queryTwo.id
</cfquery>

OR

<cfquery ... dbtype="query">
SELECT QTY, ID, DESC
FROM QueryOne JOIN QueryTwo ON queryOne.id = queryTwo.id
</cfquery>

I can't remember which or both join style QofQ uses.

P

paross1

Participating Frequently

FYI, Q-of-Q can't do the JOIN syntax and must use the pre-ANSI SQL 92 syntax where you do your joins in the WHERE clause.

Phil

A

Anonymous

I have been reading about a query of queries that part I think I understand but I am not sure how to go about Joining on the Identification number.

I do have Ben Forta's ColdFusion book if you know what chapter he explains this part.

Thank you

D

Dan_Bracuk

Inspiring

write a query of queries joining on the identification number.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded