Solved

Grouping to avoid duplicates >

Posted on 2000-05-09
10
207 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

752 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