Grouping to avoid duplicates >

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
Sumeet_kAsked:
Who is Participating?
 
GGenaCommented:
<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
 
meverestCommented:
have you tried 'select distinct' ?

cheers.
0
 
dapperryCommented:
Yeah, what's your query look like?

:) dapperry
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
GGenaCommented:
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
 
Sumeet_kAuthor Commented:
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
 
Nathan Stanford SrSenior ProgrammerCommented:
<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
 
GGenaCommented:
Nathan,

actually it is exactely the same code I advised in "Rejected Answer"
0
 
Sumeet_kAuthor Commented:
Nathans try it your query will not work here
0
 
Sumeet_kAuthor Commented:
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
 
Sumeet_kAuthor Commented:
thanks all you guys for answering this
question
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.