producing 1 time, not 3 or 2 times.

[dotJoon]

 data in myTable1

(Family_ID)  (Family_Name_Blah)  
(1)          (Fonda is a good family name.) 
(2)          (I like the name Tailor.)

data in myTable2


(First_ID)  (Family_Number)  (First_Name)  
(1)         (1)              (Henry) 
(2)         (1)              (Jane) 
(3)         (1)              (Tom) 
(4)         (2)              (Robert) 
(5)         (2)              (Liz)

I have 2 tables like the above.
myTable1 has 2 fields and myTabl22 has 3 fields.
"Family_Number" field in myTable2 is the foreign key which is connected to myTable1

Let's make the following SQL code and its result in the below

 SQL code1
select Family_Name, First_Name
from myTable1, myTable2
where Family_Name like '%Fonda%' and Family_ID=Family_Number

result1

Fonda is a good family name. Henry 
Fonda is a good family name. Jane 
Fonda is a good family name. Tom 

 SQL code2
select Family_Name, First_Name
from myTable1, myTable2
where Family_Name like '%Tailor%' and Family_ID=Family_Number

result2

I like the name Tailor. Robert 
I like the name Tailor. Liz

I like to produce target result in the below

target result1
Fonda is a good family name. Henry Jane Tom

target result2
I like the name Tailor. Robert  Liz

Can I do that with your help?

Thanks in Advance

[r937]

if you are on mysql 4.1, you can use the new GROUP_CONCAT() function

otherwise, do not try to do this with sql

you can do it relatively easily in your scripting language

what you are doing is "denormalizing" the result set from a one-to-many relationship into a one-to-one, where the first "one" is the grouping value, and the second "one" consists of a list

if you are familiar with coldfusion, you would do it like this:

<cfoutput query="foo" group="Family_Name">
<p>#Family_Name# <cfoutput> #First_Name#</cfoutput>
</cfoutput></p>

in other scripting languages, it's quite a bit more code

[dotJoon]

Your ColdFusion Code works. Thank you.