Solved

Grouping to avoid duplicates >

Posted on 2000-05-09
10
205 Views
Last Modified: 2013-12-24
I have a report structure as
One Product
Multiple Competitors
Multiple Hardware
Multiple Robotics
Multiple Platform
Mulitple Database
I am facing the problem on how to avoid
duplicates in each multiple categories
as they are different for each record ,
like one product has one database but
multiple platforms ,but I don't want
to print duplicates .

Can anyone help me in this on how to do
it with cfloop,cfoutput or cfquery

I will appreciate your help
0
Comment
Question by:Sumeet_k
10 Comments
 
LVL 37

Expert Comment

by:meverest
ID: 2795681
have you tried 'select distinct' ?

cheers.
0
 
LVL 3

Expert Comment

by:dapperry
ID: 2796344
Yeah, what's your query look like?

:) dapperry
0
 
LVL 1

Expert Comment

by:GGena
ID: 2798439
This is an answer:

<cfquery name="A">
      SELECT field1, field2      
        FROM Table      
        GROUP BY field1, field2
      HAVING id=#id#
</cfquery>      

<table border="1">
<cfoutput query="A" group="field1">
<tr>
    <td>#field1#</td>
    <td>&nbsp;</td>
</tr>
      <cfoutput group="field2">
      <tr>
              <td>&nbsp;</td>
            <td>#field2#</td>
      </cfoutput>
</cfoutput>
</table>
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Sumeet_k
ID: 2798740
There is a posiibility that table
has other fields .

I give you a example

Table View
Product Database Competitor Platform
A        D1       C1        P1
A        D1       C2        P2
A        D2       C1        P1
B        D1       C1        P1
B        D2       C1        P1
B        D3       C3        P3
A        D1                 P1
A        D1       C1  
       
Now I want to select only one product
Let say A ,and I want result as

Product A
Database D1
         D2
Product  P1
         P2
Comp     C1
         C2

Now tell me pls how to do this?  
0
 
LVL 1

Accepted Solution

by:
GGena earned 50 total points
ID: 2798774
<cfset id='A'>

<cfquery name="Q1">
   SELECT DISTINCT Database
   FROM Table
   WHERE Product = #id#
</cfquery>

<cfquery name="Q2">
   SELECT DISTINCT Competitor
   FROM Table
   WHERE Product = #id#
</cfquery>

<cfquery name="Q3">
   SELECT DISTINCT Platform
   FROM Table
   WHERE Product = #id#
</cfquery>

Product: <cfoutput>#ID#</cfoutput>
<br>

Database:
<cfoutput query=Q1>
   #Database#<br>
</cfoutput>
<br>

Product:
<cfoutput query=Q1>
  #Product#<br>
</cfoutput>
<br>

Competitor:
<cfoutput query=Q1>
  #Competitor#<br>
</cfoutput>
<br>
0
 
LVL 5

Expert Comment

by:nathans
ID: 2798782
<cfquery name="GetData" datasource="MyDatasource">
SELECT *
FROM   PRODUCTS
WHERE  PRODUCT = 'A'
ORDER BY DATABASE,COMPETITOR,PLATFORM
</cfquery>


<html>
<head>
      <title></title>
</head>

<body>
For Product: A
<cfoutput query="GetData" group="Database">
#Database#<br>
<cfoutput group="Competitor">
#Competitor#<br>
<cfoutput>
#Platform#<br>
</cfoutput>

</cfoutput>

</cfoutput>

</body>
</html>


Nathan Stanford
www.nsnd.com ColdFusion Tips Plus
0
 
LVL 1

Expert Comment

by:GGena
ID: 2803571
Nathan,

actually it is exactely the same code I advised in "Rejected Answer"
0
 

Author Comment

by:Sumeet_k
ID: 2805949
Nathans try it your query will not work here
0
 

Author Comment

by:Sumeet_k
ID: 2805953
Ggeena ,thanks for your second answer
but explore more and think about a better
solution instead of repeating the same
query loops for each output
0
 

Author Comment

by:Sumeet_k
ID: 2805956
thanks all you guys for answering this
question
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question