Selecting Multiple rows

[wbnc1902]

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.

[Newsgroup_User · Accepted solution]

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

[wbnc1902]

THANK YOU SO MUCH!!!! This was a big help. Forgive me for being new at SQL but can you explain why there is a ( ) around the inner join in the from statement? My only guess is it is some sort of subquery that joins those two tables together into one virtual table and then joins that table with the county_notes table creating one big virtual table.

Am I even close?

Thanks again!


Also thanks for keying me into to the group attribute of the cfoutput statement.

[Dan_Bracuk]

quote:

---

Originally posted by: wbnc1902
THANK YOU SO MUCH!!!! This was a big help. Forgive me for being new at SQL but can you explain why there is a ( ) around the inner join in the from statement? My only guess is it is some sort of subquery that joins those two tables together into one virtual table and then joins that table with the county_notes table creating one big virtual table.

Am I even close?

---

In this particular case, the brackets were not necessary. But, obviously they didn't hurt.

The concept you are describing has many names. I call it joining to a subquery. Others call it creating tables on the fly or derived tables. The syntax is

select stuff
from a_table join (select etc) alias_name on a_table.field = alias_name.field
etc