Prev: How to send XML file to a different server using CFHTTPin CF7
Next: How to send XML file to a different server using CFHTTP in CF7
From: wbnc1902 on 1 Feb 2008 10:35
Ok I have three tables which two tables are one to one and the other one is a
one to many. The one to one tables have all the data in one row and the one to
many has the data in multiple rows. The one to many table (named county_notes)
has notes about specific counties. The other tables (named county_bio and
county_stats) have different information about each county. The three tables
are connected by an ID.

What I want to be able to list the county information and the notes about each
county in the same report.

For example:

Record1
County: Craven
Phone: (252)123-4567
Notes:
Judy is nice
John is mean

The tables would like this:

county_bio
ID county_name
1 craven


county_stats
ID county_phone
1 (252) 123-4567

county_notes
ID notes
1 Judy is nice
1 John is mean


Obviously the above tables are just examples but you get the idea of how the
tables are set up. The problem is I can join the county_notes table to one of
the two using tables, so that is problem one. Problem 2, Coldfusion separates
each note into a different record. So I end up with record "craven" showing up
twice (once with the note about judy, and once with the note about john).

Here is the SQL that joins county_bio and county_notes:
SELECT *
FROM county_bio
LEFT JOIN county_notes
ON county_bio.ID = county_notes.county_ID

First how do I get all three tables connected? And second how do I get
ColdFusion to print each note about the county in one record?

Thanks for any and all help! I have been working on this for a few days and
am out of ideas.


From: Azadi on 1 Feb 2008 10:51
wbnc1902 wrote:
> First how do I get all three tables connected?

something like this:

SELECT *
FROM (county_bio cb INNER JOIN county_stats cs ON cb.ID = cs.ID)
LEFT JOIN county_notes cn ON cb.ID = cn.county_ID

NOTE: it is not the best practice to use SELECT *. you should instead
list actual columns you need selected.

> And second how do I get
> ColdFusion to print each note about the county in one record?

look up the GROUP attribute of CFOUTPUT tag in the docs

hth

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com
 | 
Pages: 1
Prev: How to send XML file to a different server using CFHTTPin CF7
Next: How to send XML file to a different server using CFHTTP in CF7