Solved

Grouping to avoid duplicates >

Posted on 2000-05-09
10
208 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Plug and play, no additional software required!

The ATEN UE3310 USB3.1 Gen1 Extender Cable allows users to extend the distance between the computer and USB devices up to 10 m (33 ft). The UE3310 is a high-quality, cost-effective solution for professional environments such as hospitals, factories and business facilities.

 

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

Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

Question has a verified solution.

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

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

624 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