Skip navigation
wcx08
Currently Being Moderated

Duplicates in SQL Results

Jun 27, 2011 10:57 AM

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.*,
                    OL.*,
                    P.*
     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#'
</cfquery>

 

 

 

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!

 
Replies
  • Currently Being Moderated
    Jun 27, 2011 11:09 AM   in reply to wcx08

    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.

     

    --

    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 27, 2011 2:48 PM   in reply to wcx08

    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?

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 27, 2011 4:12 PM   in reply to wcx08

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 28, 2011 8:17 AM   in reply to wcx08
    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?

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points