• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Getting what amounts to be a left outer join just has not worked for me with ColdFusion

Explorer ,
Apr 10, 2014 Apr 10, 2014

Copy link to clipboard

Copied

Dear Sir: 

I have an issue that concerns development of coldfusion queries.  I have not had unsurmountable issues with getting SQL (in <cfquery>) to work in order to add, update, delete or join (by union and union all) records.  That said, getting what amounts to be a left outer join just has not worked for me.  Let me state at this time, that I am somewhat accomplished at MS Access and in in every case, including this scenario, I have been successful in getting the output that I seek.  But at some point I see myself moving away from Access and my having to gain expertise in SQL in a non-Coldfusion environment.

If you have the time to look over the two attachments, I would appreciate your time.  Note that the table I seek to create via SQL within <cfquery> is correctly dispalyed at the top of the attachment named "Correct Output and Problematic Queries.pdf".  But again, note that this table is based on several queries developed in MS Access. 

My first attempt to get the task completed totally within <cfquery> is via a query named Recordset 8.  While I do get the counts and ratios that I wanted, the last record of the output table is corrupted.  I never could find a revised version of the query that would correctly state the data.  So I moved in another direction.  I then created a master query named Recordset3 that pulled in all of the data for the period in question.  I then developed 3 separate QoQs based on Recordset3 - named Recordsets 4, 5 & 6 to get the counts that I needed.  Recordset7 represents my attempt to join Recordsets 4 & 5 (a limitation when joining QoQs) but I proceeded no further as the joined queries now truncated to the recordset length having the fewest records.  So I did not continue this exercise to develop yet another query to pull in both Recordset 6 & 7.  The attachment cfdump.pdf displays the output of all queries (excepting Recordset 3).

Obviously, I am not on the path to properly join tables in a ColdFusion environment.  This is alarming to myself because much of what I will do in the future will involve variations of joins.  Yes I could learn SQL.  That said, my desire is to stay within a ColdFusion environment.  And it is here that I am finding that such queries are easier said than done.  For instance, the CF documentation reflects that JOIN is a function.  But when researched further JOIN is accomplished using SELECT.  And hence my exercise began.

Thanks!  Tom

Correct Output and Problematic Queries.jpgCFDUMP.jpg

Views

1.2K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Apr 10, 2014 Apr 10, 2014

Copy link to clipboard

Copied

You can definitely perform JOINs in MSAccess database queries if all of the tables reside in the same database, although the syntax for MSJet-SQL (the underlying SQL engine in the MSAccess format) may have slight differences from ANSI SQL, T-SQL (used in SQL Server), or the SQL dialect in MySQL (I'm not sure what that one is called).  You are far better off trying to get JOINS working in direct database queries than trying to stitch together a bunch of query-of-query queries.  We can help you through that if you provide more information:

  1. What is the structure of the database tables you are pulling data from?  Specify which columns would be used to join the tables.
  2. What statistics are you trying to calculate (it looks like you are doing various counts of records).

Also, you might be able to peform the percentage calculations in your output code (not in the database query) by using ColdFusion arithmetic.

-Carl V.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 10, 2014 Apr 10, 2014

Copy link to clipboard

Copied

Thanks Carl!

* There is just a single table.  I am interested in just the following field from it:

     *The name of the vendor (leadsource)

     *The date the vendor sent us a lead (date_received) that I want to reflect as a count

     *The date an appointment was scheduled (will contain a date or will be null if we are unsuccessful) that I want to reflect as a count

     *The BL_Number if the appointment results in new business (a booking) that I want to reflect as a count

So I look to create 3 separate counts for each vendor.  So vendor A might have sent us 25 leads - of which 5 appointments were successfully set - from which 2 bookings resulted.  The table to be created would list each vendor and display how many leads, appointments, and bookings occured.  The trick is for the table to list every vendor having sent us a lead regardless if an appointment booking occurred.

Vendor     Leads     Appts     Bookings

ABC          25          5               2

XYZ          10          0               0

MNO          5           2               0

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Apr 10, 2014 Apr 10, 2014

Copy link to clipboard

Copied

LATEST

I think this can be done with effective use of JOINS and subqueries.  First, write separate queries to create each of the three counts:

  1. Count records WHERE vendor sent a lead, GROUP BY vendor (if this table only contains leads, then you wouldn't need a WHERE clause since every record would be included in the count).  Your query should output two columns: vendor, and the count (maybe call it "leads_count").
  2. Count records WHERE appointment date IS NOT NULL, GROUP BY vendor.  Your query should output two columns: vendor, and the count (maybe call it "appointments_count").
  3. Count records WHERE booking number IS NOT NULL, GROUP BY vendor.  Your query should output two columns: vendor, and the count (maybe call it "bookings_count").

Once you verify each of those queries works to your satisfaction, you can combine the code to create a master query (like this pseudo-query):

     SELECT a.vendor, q1.leads_count, q2.appointments_count, q3.bookings_count

     FROM myTable AS a

          INNER JOIN (put your first query from above here) AS q1 ON a.vendor = q1.vendor  // Every vendor will be present at least once in this table, so an inner join should be okay here

          LEFT JOIN (put your second query from above here) AS q2 ON a.vendor = q2.vendor

          LEFT JOIN (put your third query from above here) AS q3 ON a.vendor = q3.vendor

     ORDER BY a.vendor

You will get a row for each vendor.  For vendors that have not made any appointments or have no bookings, those columns will be NULL.  There is a way to have Access replace any null values coming from the LEFT JOINS with something else (like a zero) involving using IFF( IsNull( column_name ), value_if_true, column_name ).

HTH,

-Carl V.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation