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

Duplicates in SQL Results

Community Beginner ,
Jun 27, 2011 Jun 27, 2011

Copy link to clipboard

Copied

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!

TOPICS
Advanced techniques

Views

546

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
LEGEND ,
Jun 27, 2011 Jun 27, 2011

Copy link to clipboard

Copied

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

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
LEGEND ,
Jun 27, 2011 Jun 27, 2011

Copy link to clipboard

Copied

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?

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
Community Beginner ,
Jun 27, 2011 Jun 27, 2011

Copy link to clipboard

Copied

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!

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
LEGEND ,
Jun 27, 2011 Jun 27, 2011

Copy link to clipboard

Copied

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.

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
Valorous Hero ,
Jun 28, 2011 Jun 28, 2011

Copy link to clipboard

Copied

LATEST
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?

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