Grouping problem...

[WaZiR006]

Hi Guys,

I have a problem... I don't seem to get this "multiple" grouping right...

I have 5 tables... The first Table "News" and then 4 more that are in relation with "News" which are "GC, RE, LS, BS"

Anyways here is my Query:

CFQUERY DATASOURCE="#DNS#" name="datab">
SELECT *
FROM News, GC, RE, LS, BS
WHERE News.ID=GC.NID and News.ID=RE.NID and News.ID=LS.NID and News.ID=BS.NID
ORDER BY news.id asc
</CFQUERY>

I've already created the relations between every &quot;NID&quot; field and the &quot;News.ID&quot;

Now i cannot get the multiple grouping right! I keep getting multiple values back! Can someone please let me know if first there is something wrong with my Query, and second how do i get the multiple grouping right?

This is what i have right now:

<Cfoutput query=&quot;datab&quot; group=&quot;ID&quot;>
bla bla
#NewsTitle#
<Cfoutput>#GeneralCategory#(from GC table)
#Region#(From RE Table)
#LearningSegment#(From LS table)
#BusinessSector#(from BS table)
</cfoutput>
</cfoutput>

Yea i know thats not how to do multiple grouping, but i can't figure it out anyways, i would REALLY appreciate it if you can help me out.


Thank you in advance,
Brad

 

[ziwacky]

Well, let's see if this is what you need.

First, you mention that you're getting multiple values. Do you mean more than one record are the same. If this is the case you should include another word (DISTINCT) in your query that will return only one of the duplicated records.

CFQUERY DATASOURCE=&quot;#DNS#&quot; name=&quot;datab&quot;>
SELECT DISTINCT *
FROM News, GC, RE, LS, BS
WHERE News.ID=GC.NID and News.ID=RE.NID and News.ID=LS.NID and News.ID=BS.NID
ORDER BY news.id asc
</CFQUERY>

Second, is this is the order of the multiple grouping you're trying to get: #ID#, #GeneralCategory#, #Region#, #LearningCenter#, #BusinessSector#?

If that's it, this is what you need to do:


<Cfoutput query=&quot;datab&quot; group=&quot;ID&quot;>
bla bla
#NewsTitle#
<Cfoutput group=&quot;GeneralCategory>
bla bla
<cfoutput group=&quot;Region&quot;>
<cfoutput group=&quot;LearningSegment&quot;>
<cfoutput group=&quot;BusinessSector&quot;
<cfoutput>
<!--Include in here the thing that
you want to see after your last
grouping -->
</cfoutput>
</cfoutput>
</cfoutput>
</cfoutput>
</cfoutput>

If you want multiple grouping you need to keep nesting cfoutputs until you get to the last group you wish to see. Then add another cfoutput with no group. This is your &quot;inner&quot; cfoutput and it will step through the records after all the groupings had been done.


This could be your output:

General Category 1
Region 1
Learning Segment 1
Business Sector 1
Bla, Bla
Business Sector 2
Bla, Bla

Learning Segment 2
Business Sector 3
Bla, Bla
Business Sector 4
Bla, Bla

Hope I make any sense. Good Luck!

Z