Skip to main content
W
wcx08
Known Participant
June 27, 2011
Question

Duplicates in SQL Results

  • June 27, 2011
  • 3 replies
  • 671 views

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!

This topic has been closed for replies.

3 replies

-
-__cfSearching__-
Inspiring
June 28, 2011
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?

D
Dan_Bracuk
Inspiring
June 27, 2011

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?

W
wcx08Author
Known Participant
June 27, 2011

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!

D
Dan_Bracuk
Inspiring
June 27, 2011

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.

A
Adam Cameron.
Inspiring
June 27, 2011

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

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices