Urgent, Can't get this grouping to work...

[WaZiR006]

Hi,

It seems like nothing is working for me. I've got help from two people already but neither of the solutions have worked for me. So im gonna post with ALL the details and see if anyone can help me.

I have Five tables: News, GC, RE, LS, BS
News is the main table... Here are the full tables:


-----

*News
ID(auto) NewsTitle
2 hello world


*GC
ID(auto) NID(News.ID) GeneralCategory
1 2 General
2 2 Services
3 2 Hosting

*RE
ID(auto) NID(News.ID) Region
1 2 Canada
2 2 US

*LS
ID(auto) NID(News.ID) LearningSegment
1 2 eLearning
2 2 online

*BS
ID(auto) NID(News.ID) BusinessSector
1 2 General
2 2 Small

-----

Those are my 5 categories... Here is my query (keeping in mind that i have already setup the table relations between EVERY NID field{in GC, RE, LS, BS} and NEWS.ID)

<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
</CFQUERY>

And here is what the grouping method that was suggested but doesn't work:

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


Now this doesn't work for me. I don't know why??? I keep getting MULTIPLE values(repeated) so i can't get the grouping to work at all. Can somebody look over my situation and let me know what im doing wrong? I feel so stupid, cause i've been asking for help on this matter for so many times and i still can't get it right.

I REALLY appreciate anyone for taking the time to answer me, i owe you big time. And again THANK YOU TONS to the people who have helped me on this forum! You guys/gals are the best!!!


Much Thanks,
Brad

 

[CFHub]

This thread is also available here:

http://cfhub.com/discussion/viewmessages.cfm?Forum=5&Topic=882

 

[WaZiR006]

Yea i know, But thats a different forum and still no one has been able to give an answer. I posted here aswell to see if anyone here can give me a hand. I have posted the FULL info of my query, CFOUTPUT, Tables AND my results in that post. So if you like, please go here (

http://cfhub.com/discussion/viewmessages.cfm?Forum=5&Topic=882

) and view the last msg (that i just posted) and see if you can help me??

Its been now 2 weeks and i still haven't been able to figure out how to fix this...


Thanks,
Brad

 

[kathryn]

OK, the big thing that I see is that every group you want must be in your order by clause in your SELECT statement, in the order of the groupings. Have you tried that? If so, exactly what error are you getting? Kathryn

 

[tleish]

I don't believe you can only use one GROUP parameter per <CFOUTPUT> embeded tags.

In other words:

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

is the same as writing:

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

Cold fusion will only use the first GROUP tag and ignore the rest

 

[ram123]

this is very straight forward. You required only group because all the four tables are dependents to the News table. May be your table designing is wrong.
I think your requirement is like this.
gc should depend on the news table.
re should depend on gc table.
ls should depend on re table.
bs should depend on ls table.
Because if all the four tables depends on main table(News table), you need only two tables (news table and another table with all the data).
Am i correct? Let me know.

 

[WaZiR006]

Hi guys & gals,

kathryn - Yes i've tried that, its in my new SQL statement located at that URL i posted.

tleish - I dunno much about CFOUTPUT (cause i've been working on it like crazy and can't get it right) But from the results i get, it turns out those two grouping ways gives me the same results. So is the answer simply that i can't do nesting with multiple tables and rows???

ram123 - Thats an interesting idea, i went to the SQL design and looked at my design again. One thing is for sure, i cannot relate RE to GC. Because it requires a UNIQUE filed to be related to. For example i cannot related RE.NID to GC.NID since GC.NID is not unique. My table relation as of this moment is like this : News.ID-->GC.NID, News.ID-->RE.NID, News.ID-->LS.NID, News.ID-->BS.NID

However what you said does make sense! But still i can't relate RE to GC because i need to use the value in GC.NID which is the same as the New.ID(auto number). I guess one other solution would have to be adding the columbs from the other tables to GC (i.e. &quot;Region&quot;, &quot;LearningSegment&quot;, &quot;BusinessSector&quot and then im pretty sure i can make it work. But, doesn't that mean in a few months that table is gonna be HUGE ?? and doesn't that mean that the longer that table becomes, the longer it will take SQL to search through it?? Cause it'll have to go through ALL the values in the tables and match the NID and pick the GeneralCategory or Region, etc. So i don't think that would work for me. What do you think?

Again i really appreciate all the help, i've been trying to get this right but it seems as if im missing out on something.


Thanks,
Brad

 

[kathryn]

OK, I think we all need some clarification on this. To go back to the original post...

In the tables that you show are you saying that This news headline applies to general categories General, Services and Hosting, and that it applies to regions US and Canada, etc? So if I queried all headlines for Canada with no other criteria, then I want this record to show. Or if I query all Hosting category headlines, again with no other categories, that I want this to show. However, if I query Hosting and Japan, this will not show.

Is this correct? Kathryn

 

[ram123]

Ok, if you want keep the same table relation, try this one.

<cfquery name = &quot;xyz&quot; datasource=&quot;zbz&quot;>
select a.id as groupid, a.newstitle, b.generalcategory, c.region, d.learningsegment, e.businesssector
from news a, gc b, re c, ls d, bs e
where a.id = b.nid and b.nid = c.nid and c.nid = d.nid and d.nid = e.nid>

<cfoutput query = &quot;xyz&quot; group = &quot;groupid&quot;>
#newstitle# <br>
<cfoutput>
#generalcategory#
#region#
#learningsegment#
#businesssector#
</cfoutput>
</cfouput>

You cannot nest region inside generalcategory, learningsegment inside region, businesssector inside learningsegment becuase there is no relation for region and generalcategory and so on. Just examine your tables .
for example how can you findout canada region for Hosting category or Small businesssector for US region.

I think the best solution is you need redisign the tables like this.

New table keep it same
the fields are NID, Newstitle


GC table keep it same
the fields are GCID, NID, GeneralCategory

RE table remove the NID field and add GCID field
the fields are REID, GCID, Region

LS table remove the NID field and add REID field
the fields are LSID, REID, LearningSegment

BS table remove the NID field and add LSID field.
BSID, LSID, businesssector

and the cfquery would be like this


<cfquery name = &quot;xyz&quot; datasource=&quot;zbz&quot;>
select a.nid, a.newstitle, b.GCID,b.generalcategory, c.reid, c.region, d.lsid, d.learningsegment, e.bsid, e.businesssector
from news a, gc b, re c, ls d, bs e
where a.id = b.nid and b.gcid = c.gcid and c.reid = d.reid and d.lsid = e.lsid>


then your code should work to display.

<Cfoutput query=&quot;xyz&quot; group=&quot;NID&quot;>
bla bla
#NewsTitle#
<Cfoutput group=&quot;GCID&quot;>
#GeneralCategory#
<cfoutput group=&quot;REID&quot;>
#Region#
<cfoutput group=&quot;LSID&quot;>
#LearningSegment#
<cfoutput>
#BusinessSector#
</cfoutput>
</cfoutput>
</cfoutput>
</cfoutput>

Let me know.

 

[ram123]

oops . typo mistakes in second query i mention. in where condition i mention a.id = but it is a.nid

 

[CFHub]

Yes, I think a little dbase restructuring is in order

Nice turnout though, great to see so many people pitching in

 

[WaZiR006]

Hi,

kathryn - That is correct. I need that feature for the &quot;admin&quot; site where i show the news headline and it shows which categories it belongs to. Also if im doing a search, lets say for generalcategory &quot;small business&quot; AND region &quot;Canada&quot;, it will not give me the results.

ram123 - Thank you for your response. Well the first one didn't work, gave me the exact same result (repeated fields)

Then i went and totally redesigned my tables. Also i setup my table relations according to what you showed in your &quot;WHERE&quot; statement. However i get no results back. Im not undrestanding this new table design. How would this help me out when i do a search? For example, for the news title &quot;bla bla bla&quot; with News.ID=3, i want to sometimes search for either GeneralCategory, or Only Region, Or be able to search for combination of LearningSegment and BusinessSector, OR be able to search for EVERY entry in the four tables (GC, RE, LS, BS) where they were given the relation to the News.ID one.

I don't see how that could be done with this new table relation. If GC.NID=RE.GCID and Re.REID=LS.REID, then that means i can't just search for the region since that specific row is attached to GC's ID and not to the main News table. know what i mean?

I wonder if i did something wrong when i related the tables. Although i went over them 4 times and made sure that they are exactly the way you listen them. Any ideas?

Thank you for taking the time to reply, i appreciate whoever that takes the time to post any reply.


Much Thanks,
Brad