0

I'm trying to use Query of Queries based on two separate queries but I'm not getting the result I want. I would consider myself beginner in both SQL and Coldfusion. I'm using Coldfusion 2016 and SQL Server 2012.

Initial request: Give me the name of all applicants whose fee was waived and scheduled an audition.

dsn1.table1 - applicant table

dsn1.table2 - audition table

<cfquery name="getApplicants" datasource="dsn1">
    SELECT dbo.table1.STU_ID, dbo.table1.NAME, dbo.table1.FEE_STATUS
    FROM dbo.table1, dbo.table2
    WHERE (dbo.table1.STU_ID = dbo.table2.STU_ID) AND FEE_STATUS = 'WAIVED'
</cfquery>

THIS WORKS!

Follow-up request: Some applicants did not know their fees were waived so they still went through the payment process. Give me all the applicants that had their fees waived AND scheduled an audition AND did not pay.

dsn2.table3 - contains the payment information. There are multiple entries for an applicant (STU_ID) that timestamps their application process. Sample:

John Doe
123456789 - TENTATIVE - timestamp1
123456789 - PAID - timestamp2
123456789 - EMAILED - timestamp3
123456789 - FINALIZED - timestamp4

Jane Doe
234567895 - TENTATIVE - timestamp1
234567895 - TENTATIVE - timestamp2
234567895 - TENTATIVE - timestamp3
234567895 - CUSTOMER CANCELLED BEFORE PROCESSING PAYMENT - timestamp4

John Smith
345678951 - TENTATIVE - timestamp1
345678951 - TENTATIVE - timestamp2
345678951 - TENTATIVE - timestamp3
345678951 - TENTATIVE - timestamp4
345678951 - PAID - timestamp5
345678951 - FINALIZED - timestamp6
345678951 - EMAILED - timestamp7

My approach was to get the non-payees first:

<cfquery name="getNonPayees" datasource="dsn2">
    SELECT  dbo.table3.STU_ID, dbo.table3.PAYMENT_STATUS
    FROM dbo.table3
    WHERE dbo.table3.PAYMENT_STATUS <> 'PAID'
</cfquery>

THIS WORKS!

After that, I was going to use Query of queries to match the results from getApplicants and getNonPayees:

<cfquery dbtype="query" name="QofQ">
    SELECT getApplicants.NAME
    FROM getApplicants, getPaymentStatus 
    WHERE getApplicants.STU_ID = getPaymentStatus.STU_ID
</cfquery>

ERROR: The select column reference [getApplicants.NAME] is not found in table [getApplicants].

<cfquery dbtype="query" name="QofQ">
    SELECT DISTINCT getApplicants.NAME
    FROM getApplicants, getPaymentStatus 
    WHERE getApplicants.STU_ID = getPaymentStatus.STU_ID
</cfquery>

ERROR: The select column reference [getApplicants.NAME] is not found in table [getApplicants].

<cfquery dbtype="query" name="QofQ">
    SELECT *
    FROM getApplicants, getPaymentStatus 
    WHERE getApplicants.STU_ID = getPaymentStatus.STU_ID
</cfquery>

or

<cfquery dbtype="query" name="QofQ">
    SELECT NAME
    FROM getApplicants, getPaymentStatus 
    WHERE getApplicants.STU_ID = getPaymentStatus.STU_ID
</cfquery>

No error but I get zero records for the QofQ. Please advise. Thank you!

Leigh
  • 28,765
  • 10
  • 55
  • 103
airpolgas
  • 77
  • 7
  • 1
    When in doubt, look at your data. In this case, the columnlist from getApplicants is a good place to start. – Dan Bracuk Jul 05 '16 at 18:13
  • I'm sorry. I will need to delete that last code and error. I tried "NAME" as a generic (like table1, table2, etc.) so dummy me tested NAME instead of the actual variable. What I ended up was no error but zero record count. – airpolgas Jul 05 '16 at 18:34
  • Have you considered not using Q of Q and simply adding another filter to your database query? – Dan Bracuk Jul 05 '16 at 18:56
  • I thought about that, but I don't know the syntax. I've read up a lot on querying two tables (which I used on the getApplicants), but two tables from one dsn and another table from another dsn threw me off (read: I don't know), so I tried to split it and use QoQ instead. – airpolgas Jul 05 '16 at 19:03
  • I didn't notice the different dsns. Since this is sql-server, you have options. If the db's are on the same server, you can get permissions set up and qualify your tablenames with the database and schema names. If not, linked servers are a better way of dealing with large amounts of data than is ColdFusion. Getting back to your Q of Q that returned no records, that might be accurate. – Dan Bracuk Jul 05 '16 at 19:21
  • Wow! I just realized the QoQ code I used was from you! http://www.justskins.com/forums/query-of-queries-combining-83724.html Both datasources are on the same SQL server, and I'm using the same credential. I'll read up on permissions and hopefully that gets me somewhere. – airpolgas Jul 05 '16 at 20:51
  • 1
    That would explain why it doesn't work very well. – Dan Bracuk Jul 05 '16 at 21:36

1 Answers1

0

If you can't join the two datasets via the database which is how I would approach it, you could combine the two queries (after matching up the columns - i.e. make sure the ID, Name & Status match in the results). You can use ColdFusion to loop through the 2nd query adding all the rows of that query to the first. Then, you'd have one query (the first one) with all the data.

So...

query1 - ID, Name, Status (with the requisite where clauses)

query2 - ID, Name, Status (with the requisite where clauses)

Then loop through query2 adding the results to query1. QueryAddRow, I believe...

This will not be very fast, in fact it will be SLOW.

Once that is done, you can Q of Q.

That being said...try to do as much as possible at the database as others have said.

quig
  • 64
  • 1
  • 6