Query of Queries and Join Not Working

And it is a shame that it doesn't support outer joins, but the above example is very similar, using an INNER JOIN, the different between the two is the fact that appid in Q1 and Q2 must exist for a record to be returned using an INNER JOIN. So if q1.appid has a value of 3 but q2 doesn't have a 3 for appid, it won't return that row whatsoever, since q2 doesn't have the value of 3 for appid in any of its rows.

But with OUTER JOINS, it returns NULL if it cannot find the same value in the second table that is being joined. Only inner joins are supported by Coldfusion Queries of Queries, and should work out well for you.

<cfquery name="Q3" dbtype='Query'>
   SELECT  Q1.AppID, Q1.AppAcronym, Q1.AppPriority, Q1.AppType, Q1.AppStatus, Q2.SysName, Q2.SysStatus, Q2.SysType, Q2.SysOS,  Q2.SysOSType

   FROM    Q2, Q1
               Q2.AppID = Q1.AppID    
</cfquery>

Goodluck!

andw928 is correct in that OUTER JOINS are not supported.

But an INNER JOIN is a wrong answer and will not give you what you need.

Instead you have two options

1) Best option - do the outer join in Oracle.  It can do it and from the above code I see no reason why you can't let Oracle do the join.

2) Manually do the work of the query of queries.  You manually create a query object and construct the outer join results.  This is very painstaking, but if you can't let the DB do it for you it is an alternative.  Let me know if you need sample code on how to do this.

He can't do the join in Oracle, it is 2 datasources and 2 different DBMS's. What's wrong with the INNER JOIN? The fact that it won't return a row if the same appid doesn't exist in both tables is a given, that's the reason for the INNER JOIN. I think tfstarks, has same appids in both queries.

Also, if he doesn't he can do inner join, and then we can think of some light-weight code to get the rows that don't correspond to each other in both tables.

UNfortunately, you are both right - neither is working. When I reconfigured the query using the "inner join" syntax as follows, I got almost the same error I was getting before. The code and the error are below. I'm at my wits end, and am leaving for the day. I'll check back in later tonight to see if yall had any ideas....any of which are greatly appreciated...afterall, I've got to believe I'm not the only person in the world who is trying to get this kind of solution to work...am I? Oh, and "andw928", just FYI, I tried doing it "exactly" as your example, but that only returned multiple duplicates of a single record, so I must have done something wrong there too, because there are literally hundreds of distinct records that this query should return.

HERE IS THE REVISED CODE (the first two parts didn't change so I'm not including them again):

<cfquery name="Q3" dbtype='Query'>
   SELECT  Q1.AppID, Q1.AppAcronym, Q1.AppPriority, Q1.AppType, Q1.AppStatus, Q2.SysName, Q2.SysStatus, Q2.SysType, Q2.SysOS, Q2.SysOSType
   
   FROM  Q2 INNER JOIN
         Q1 ON Q2.AppID = Q1.AppID
</cfquery>

HERE IS THE ERROR TEXT:

Query Of Queries syntax error.
Encountered "INNER" at line 0, column 0.  

20 : </cfquery>
21 :
22 : <cfquery name="Q3" dbtype='Query'>
23 :
24 :    SELECT  Q1.AppID, Q1.AppAcronym, Q1.AppPriority, Q1.AppType, Q1.AppStatus, Q2.SysName, Q2.SysStatus, Q2.SysType, Q2.SysOS, Q2.SysOSType

--------------------------------------------------------------------------------
SQL    SELECT Q1.AppID, Q1.AppAcronym, Q1.AppPriority, Q1.AppType, Q1.AppStatus, Q2.SysName, Q2.SysStatus, Q2.SysType, Q2.SysOS, Q2.SysOSType FROM Q2 INNER JOIN Q1 ON Q2.AppID = Q1.AppID  

A good thing to consider is can you have the SQL server create a linked server to the Oracle server.  Then you can do it all using the SQL server datasource.  Let me know if this idea sounds interesting...

Basically to do this you would have to have access to the SQL server.  Then you create a connection to the Oracle server.  Then in SQL you can access like this:

<cfquery name="Q1" datasource="#SQLDATASOURCE#" username="#USE1#" password="#PASS1">
    SELECT
        SQLtable.AppID, SQLtable.AppAcronym, SQLtable.AppPriority, SQLtable.AppType, SQLtable.AppStatus,
        Oracletable.SysName, Oracletable.SysStatus, Oracletable.SysType, Oracletable.SysOS,  Oracletable.SysOSType
    FROM
       ASQLserverDB SQLtable
       LEFT OUTER JOIN linkedservername.schema.owner.tablename Oracletable
      ON SQLtable.AppID = Oraclertable.AppID
</cfquery>

I made everything wordy there just to show how it would be accessed, so I used aliases as well.

Let me know if this doesn't make sense or if you need more details to try it out.

Can you post the output of Q1 and Q2 seperattely? Post like 10 records from each one, I want to see how it looks like. If you can post the entire queries that would be good too.

Also here is the by hand example:  (WARNING - it is messy and I would use as a last resort, but have had a need once before where it was the only option)

<cfset Q3 = QueryNew("AppID,AppAcronym,AppPriority,AppType,AppStatus,SysName,SysStatus,SysType,SysOS,SysOSType")>

<cfloop query="Q1">
  <!--- Add new row to the query --->
   <cfset newRow = QueryAddRow(Q3)>
    <!--- Add in each column in current record from Q1 --->
    <cfset Temp = QuerySetCell(Q3, "AppID", Q1.AppID)>
    <cfset Temp = QuerySetCell(Q3, "AppAcronym", Q1.AppAcronym)>
    <cfset Temp = QuerySetCell(Q3, "AppPriority", Q1.AppPriority)>
    <cfset Temp = QuerySetCell(Q3, "AppType", Q1.AppType)>
    <cfset Temp = QuerySetCell(Q3, "AppStatus", Q1.AppStatus)>
    <!--- See if any matching records in Q2 as would happen in an OUTER JOIN --->
    <cfloop query="Q2">
        <cfif Q2.AppID EQ Q1.AppID>
            <!--- Add columns from record that matches --->
               <cfset Temp = QuerySetCell(Q3, "SysName", Q2.SysName)>
               <cfset Temp = QuerySetCell(Q3, "SysStatus", Q2.SysStatus)>
               <cfset Temp = QuerySetCell(Q3, "SysType", Q2.SysType)>
               <cfset Temp = QuerySetCell(Q3, "SysOS", Q2.SysOS)>
               <cfset Temp = QuerySetCell(Q3, "SysOSType", Q2.SysOSType)>
               <cfbreak>
        </cfif>
    </cfloop>
</cfloop>

You may want to add in so that NULL or blank or default values get added if no record is found in the left join search portion - I didn't add that because it depends on your needs on what would go there.  It would be safest to add blanks if you aren't sure... Then you won't get errors.

But as I said I would still stick with the Linked Server if that is an option first.  Or even if not I thought of one other thing.

You can use an OPENQUERY on the SQL Server if you can write a stored procedure.  Then you can have SQL read the values from Oracle into SQL server - do the outer join there and basically have only one query from CF to the SQL server.  Open queries are very easy and require less access to teh SQL server - only the ability to do stored procedures - so it may be easier to implement.

Many options - let me knwo whic way to explore and I can give more details that you may need.

I don't know about mrichmons solution with large data, you are basically looping x times where x is the number of records in query 1 * the number of records in query 2. Imagine 100 records in 1 and 1000 in the other = 100,000 loops! That's not mentioning the other stuff that is happening within that loop itself!

It may work though. Post us the data returned from Q1 and Q2. So we can inspect it.

Yes it is a very bad solution the larger the data gets.  It is also putting the job of T-SQL into CF which is not a good idea if you can help it.  Basically it is trying to code by hand what a join does.  It does work, and if it is the only way to get teh results you need then that has to take priority over efficiency.

As I said - one time I had no other option due to limited server access so it was a last resort.  

However, as I mentioned I think the other two options (linked server or OpenQuery) are better options - you are letting the database do what it was designed for.

Anytime you can have the database do a join it is better than even a query of queries for large datasets.

Gentlemen, I'm sorry you are working on this without some significant background. But, I thought that by telling you that the data was coming from two different places would be sufficient. I was wrong. I am in a highly restricted environment. I have no access to administration, only development via named datasources in an "application.cfm" file. One named source is an Oracle DB and one is a SQLServer. Both have multiple tables that I need to run multiple queries on. According to the way this environment is set up, I HAVE TO be create my queries in CF, meaning that I must join them somehow. This means no linked servers or other forms of procedural queries that we would normally use in the DB clients. That is why I am turning to you CF experts - I need a miracle.

Reverend Tom on his knees.

Example of the data returned from both queries would help! the INNER JOIN syntax I showed was supposed to work. There has to be something wrong with teh data returned from Q1 or Q2.

why not combine the two queries?
so instead of all those 3 queries,

from
<cfquery name="Q3" dbtype='Query'>
   SELECT  Q1.AppID, Q1.AppAcronym, Q1.AppPriority, Q1.AppType, Q1.AppStatus, Q2.SysName, Q2.SysStatus, Q2.SysType, Q2.SysOS,  Q2.SysOSType
   FROM    Q2 RIGHT OUTER JOIN
               Q1 ON Q2.AppID = Q1.AppID    
</cfquery>

to:
use this instead?

<cfquery name="Q1" datasource="#DATA1#" username="#USE1#" password="#PASS1">
    SELECT
        Q1.AppID, Q1.AppAcronym, Q1.AppPriority, Q1.AppType, Q1.AppStatus,
        Q2.SysName, Q2.SysStatus, Q2.SysType, Q2.SysOS, Q2.SysOSType
    FROM
         AnOracleDB Q2 RIGHT OUTER JOIN
        ASQLserverDB Q1 ON Q2.AppID = Q1.AppID    
</cfquery>

You can't he is using 2 datasources from 2 different database managements servers. Oracle and SQL Server.

Then in this situation you will need to use the manual method.

This is the only way to get the same results as when using an outer join.

tfstarks, do any of the 2 queries return an APPid that is not the same in the other query? In other words, do both queries return the same Appids, or is there an instance where 1 query might be missing the appid given by the other query?

hehe.. i see.. ive never read the next comments.
even inner joins and outer joins are not supported.
not even subqueries.... :(
use only old simple sql statements using where clause and = clase.

as they have said, you will have to do it manually.
either loop through each records and do a lookup (yuck - no choice.)

or

find a different SQL statements to comeup with the same result...
hm.. lets see.. i wonder if *= (for right join) is still supported like

<cfquery name="Q3" dbtype='Query'>
   SELECT  Q1.AppID, Q1.AppAcronym, Q1.AppPriority, Q1.AppType, Q1.AppStatus, Q2.SysName, Q2.SysStatus, Q2.SysType, Q2.SysOS,  Q2.SysOSType

   FROM    Q2 , Q1
WHERE
               Q2.AppID *= Q1.AppID    
</cfquery>

you have to find an alternative to right join.
not sure if the following will give the same result.
subqueries are not supported.. oh bugger.

<cfquery name="Q3" dbtype='Query'>
   SELECT  Q1.AppID, Q1.AppAcronym, Q1.AppPriority, Q1.AppType, Q1.AppStatus, Q2.SysName, Q2.SysStatus, Q2.SysType, Q2.SysOS,  Q2.SysOSType

   FROM    Q2 , Q1
WHERE
               Q2.AppID = Q1.AppID    
OR
               Q2.AppID = Q2.AppID
OR
               Q2.AppID = null
</cfquery>


SQL syntax for query of queries
http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/using_39.htm#wp1175636

hmmm.. just curius.. probably

both q1 and q2 then result is
 q1,q2
only q1 then result
 q1,nulls
only q2 then result
 nulls,q1
and you need "both q1 and q2 " and  "only q2"

probably...  (not sure)

<cfquery name="Q3" dbtype='Query'>
   SELECT  Q1.AppID, Q1.AppAcronym, Q1.AppPriority, Q1.AppType, Q1.AppStatus, Q2.SysName, Q2.SysStatus, Q2.SysType, Q2.SysOS,  Q2.SysOSType
   FROM    Q2 , Q1
WHERE
               Q2.AppID is not null
</cfquery>

'mrichmon', what do you mean when you say, "the manual method"?, and

'andw928', yes, I did use your exact code, and I'm sorry I didn't say that I did, because I thought I had when I mentioned that all it did was return hundreds of records of duplicates of the same record. And yes, I know from doing this in my SQLServer client that there are many distinct records that this query should return. Here's the example output from your query...just imagine it repeated several hundred rows worth. :-)

AppID     Acronym   DR Pri    App Type   App Status      Sys Name       Sys Type      Sys Status        Sys OS            Sys OS Type
   3          ABCDE        6          APPL             DEV             DLRP2           SERTX         Inactive       SUN SOLARIS         SOLARIS

In answer to your other question, 'andw928', "Oracle" is a System database, so there are far fewer "AppIDs" per record, still, I'd say, that 2/3 of the records have the AppID field populated, whereas it's a mandatory field for the SQLServer table...being that it's the key field for that table. Therefore, yes, therefore, the chance that records are going to exist without appids in Q2 is very high. VERY HIGH. Hence the reason I wanted to do the outer join using Q1.

Thanks again everyone, I'm still looking for my miracle! It's a brand new day!

Rev. TFS

Forgive me 'andw928'!!! Your solution did actually work! When I went back and plugged in the "<cfdump>" tag, then it returned the individualized results. So, you get credit for the solution. I'll go back and accept the answer. However, I'm still at a loss as to know how to know format my results into recognizable tables! Oh God, if its not one thing it's another, eh, guys? Oh well, at least I've got this hurdle crossed, and I've got you to thank for this particular miracle! Thanks again for putting up with this particular 'messenger'.

Thankful Tom

Not a problem. I knew that join had to work, but if you say there are fewer AppIDs per record in the System database then you can still be returning partial records instead of everything. Just post here if you still have problems with something.

Say, I'll take you up on that offer 'andw928'. Is there anything else you can tell me about that "<CFDUMP>" tag? I've been all over Google, EE, CF, LiveDocs, and a few other places this morning trying to hunt down examples of how to use it to format result sets, but I keep coming up dry...or at least not with what I'm expecting. Is there a "<CFDUMP>" formatting resource that you, or anyone else reading this who might feel charitable, might point me to? I'd certainly appreciate it. Otherwise, I'm going to have to go back to the drawing board because I have strict parameters in which I need to display the results, and I know I'll be able to do that once I figure out how to format this "<CFDUMP>" to let me do that. It's just a matter of finding the proper CSS or parameters or something, and if yall have pointers to that, I'd sure appreciate you throwing them my way!

Thanks again!

RTFS

No, cfdump is just a debug tag, it basically shows what the query returned. You can get the exact same results if you output the query using <cfloop> or <cfoutput query>. <cfdump> basically just shows the contents of a structure, array, variable, or query. You can use cfdump on basically any Coldfusion variable that comes to mind. For example, if you go to this page:

index.cfm?page=1&id=730

Then do: <cfdump var="#url#">, you will see the page and id and the values for both of these, since anything in the URL is in hte URL structure. You can dump forms, queries, whatever comes to mind, it is a fast debug tool.

Once you know what you want to do, you don't use cfdump anymore, instead you can loop the contents of that join that you have:

<cfloop query='q3'>
    #appid# - #sysname#<br />
</cfloop>

The cfdump tag is there to help you, it is for debugging.