3 Replies Latest reply on Apr 10, 2014 1:01 PM by Carl Von Stetten

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

    NVL-Tom Level 1

      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

        • 1. Re: Getting what amounts to be a left outer join just has not worked for me with ColdFusion
          Carl Von Stetten Adobe Community Professional & MVP

          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.

          • 2. Re: Getting what amounts to be a left outer join just has not worked for me with ColdFusion
            NVL-Tom Level 1

            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

            • 3. Re: Getting what amounts to be a left outer join just has not worked for me with ColdFusion
              Carl Von Stetten Adobe Community Professional & MVP

              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.