5 Replies Latest reply on Jun 28, 2011 8:17 AM by -==cfSearching==-

    Duplicates in SQL Results

    wcx08 Level 1

      This probably belongs better in a SQL forum...Or mabe somewhere else on THIS forum...But I'm in a bind and don't know what else to do.


      Here's my query:



      <cfquery name="getCompanyOrders" datasource="#application.DSN#">
           SELECT         O.*,
           FROM           tbl_Order O
           INNER JOIN     tbl_OrderLine OL ON OL.fk_orderID = O.pk_orderID
           INNER JOIN     tblProduct P ON P.pk_productID = OL.fk_productID
           WHERE          P.fk_supplierID = #attributes.companyID#
           AND            O.orderDate BETWEEN '#datebeg#' AND '#dateend#'




      This gives me everything I want, but I want it to order these results by a unique identifier in tbl_Order, pk_orderID.  This result gives duplicate pk_orderID's, and I was wondering how I can select distinct ones only.  When I use "select distinct", it removes all the other information I need from the other joined tables.


      Any ideas?


      Thank you!

        • 1. Re: Duplicates in SQL Results
          Adam Cameron. Level 5

          Yep, you're right this is not a CF question, so it's not a good fit for these forums.  You seem to already know this, too ;-)


          However you could try doing your DISTINCT stuff in a subquery, and then rejoin that back with the rest of the columns you need.  It's difficult to answer too thoroughly without knowing the table schemas though.


          You should also get rid of all the SELECT * stuff and just get the columns you want.  Plus don't hard-code your dynamic values in your WHERE clause into the SQL statement, parameterise them instead.




          • 2. Re: Duplicates in SQL Results
            Dan Bracuk Level 5

            This statement, "When I use "select distinct", it removes all the other information I need from the other joined tables.", sounds suspicious.  What are you looking at that makes you think it's true?

            • 3. Re: Duplicates in SQL Results
              wcx08 Level 1

              I don't entirely understand your question...As you can see I'm requesting to select everything from tblProduct, for example.  If I do a query of queries on this query, to select ONLY distinct order IDs, it doesn't include all the information from the other tables when I do a cfdump.  I'm not entirely sure why.


              So I guess to answer your question, I'm doing a cfdump of the query after I do it, so I can see a quick example of the results before I start working with them.  I have to get a set of results that I need.  I just need to play with it some more.  I probably shouldn't have made this post in the first place.


              Thanks though!

              • 4. Re: Duplicates in SQL Results
                Dan Bracuk Level 5

                In your db query, using select distinct would probably not change the results.  However, if I read between the lines and make a couple of assumptions, if you put an order by clause in your db query, the group attribute of cfoutput may help you achieve your goal.

                • 5. Re: Duplicates in SQL Results
                  -==cfSearching==- Level 4
                  This result gives duplicate pk_orderID's



                  I am confused.  Are they really duplicates? Because based on your query it seems like you are either


                            a) asking for too much information OR

                            b) are simply unsure how to display that information properly.


                  With most order systems, a single order can have multiple line items associated with it:


                     OrderID #5
                        [1]   item x ...
                        [2]   item y ...
                        [3]   item rr ...


                  So if you request all line items AND the parent "orderID" (exactly what your query is doing) of course the same "orderID" will appear multiple times. It will appear once for each of its line items. But they are not duplicates.


                    ie  OrderID, OrderLineID,
                        [5]   [1]   item x ...
                        [5]   [2]   item y ...
                        [5]   [3]   item rr ...


                  ... which brings me back to orginal the question: are you asking for too much information OR just unsure how to display things properly?