Query of Queries on 2 datasources

[Guest]

I'm trying to do a query of queries on 2 different datasources. I run a gaming site and have a 2 programs that are used for tracking. One is written in php, by someone else, that tracks members point status. The other, I wrote in CF, allows members to set up treasure priorities. Both are stand alone on my server. What I am trying to do is set it up so the program I wrote in CF will display the # of points each member has. I know how to query the separate tables but how can I combine them? Also, how can I set it up so that it only shows the members that match. i.e. I want to make sure that if John Smith has 100 points, the query will show John Smith's 100 points on my table. Here is what the query looks like as I have done it. As you can see, I'm confused about the FROM and WHERE statements. I played with it last night and couldn't get it to work. Thanks.

[Newsgroup_User]

MIGhunter wrote:
> FROM what goes here?


Since this is a query of queries, the names of the queries go there.

I.E.

FROM dsA, dsB
WHERE user_name = member_name

[Dan_Bracuk]

The ValueList() function would probably come in handy here. Usage is described in the cfml reference manual. If you don't have one, the internet does.

[LogicBlast · Accepted solution]

You could try the same pinciple/idea of what I posted in thread http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid=1&catid=7

Copied/Pasted from that thread:

You could read thru the first query and add the contents to a temp query assigning a sort by field and then read thru the second query and add it to the same temp query with a sort by field. Then do a query of that temp query.

[Guest]

This leads me to a new question.

How can I output my query in my current table without rewriting everything?

My original query is for the table and if I use the above code with #combinedNames.member_earned# the table is 1 number. Like this: Preview

It's the 2nd column. Now, If I break the cfoutput and put in a new cfoutput with the combined queries, it totally jacks up the table all over the page. Hope that makes sense.

[LogicBlast]

Instead of breaking the query, try using, for column two (instead of a cfoutput) a
<cfloop query="combinedNames">

</cfloop>

Unless I'm misunderstanding you.

[Guest]

I actually got it working using the combined query instead of 2 queries. The only problem is that if the player is organized and declares a priority before going to their 1st event, their name won't show up. Of course this shouldn't really matter since they have to participate before they can have a priority. Here is how I got it to work:

[LogicBlast]

I didn't know you could do that. Thanks for teaching me something new!

[Guest]

I get a lot of help from EasyCFM. One of the guys there posted a Link on how to do outter joins on QofQs. That way you get all the information from both tables, even if they aren't the same.