The following functions may do the trick for you. They make the explicit assumption that the description in the phone type table makes a valid field name.

FUNCTION onecontact
PARAMETERS contact
SELECT phones.*,phone_type.desc ;
  FROM phones,phone_type;
 WHERE con_id = contact AND phones.type_id=phone_type.type_id;
  INTO CURSOR curPhones
lcFields = "SELECT con.name"
lcTables = " FROM contacts con"
lcWhere = " WHERE con.id = m.contact"
SCAN
  STORE ph_id TO ("id"+TRANSFORM(RECNO()))
  lcFields = lcFields + "," + TRIM(curphones.desc) + ".phone_no AS " + TRIM(curphones.desc)
  lcTables = lcTables + ",curphones " + TRIM(curphones.desc)
  lcWhere = lcWhere + " AND " + TRIM(curphones.desc) + ".ph_id = id" + TRANSFORM(RECNO())
ENDSCAN
lcSql = lcFields+lcTables+lcWhere
&lcSql

FUNCTION allcontacts
USE phones
lcEmpty = SPACE(LEN(phone_no))
SELECT 0
USE phone_type
lcFields = "SELECT con.name"
lcTables = " FROM contacts con, phones ph"
lcWhere = " WHERE con.id = ph.con_id "
lcGroup = " GROUP BY con.name INTO CURSOR curPhones"
SCAN
  STORE type_id TO ("id"+TRANSFORM(RECNO()))
  lcFields = lcFields + ",MAX(IIF(type_id=id"+TRANSFORM(RECNO())+",ph.phone_no,lcEmpty)) AS " + TRIM(desc)
ENDSCAN
lcSql=lcFields + lcTables + lcWhere + lcGroup
&lcSql

>Hi Everyone:
>
>I have a one-to-many table design (now that i know what one is :-) ) and am trying to use a select statement to get the data in one row, so to speak.
>
>one table has contacts, another has phone types (ie home, home2, cell), and the third table has the contact_id, the phone_id (ie which type of phone), and the phone #. Obviously, a lot of people have more than one phone # so for each contact_id in the third table, there is more than 1 result.
>
>I want to select this information in a query so it shows up in one record, not multiple records, based on each person's id (or for this example, their last name). For example, Joe Smith will have 4 different phone numbers. His contact_id is 1.
>
>select f_lname, f_phone from t_contacts, t_phone where;
>t_contacts.f_id = t_phone.contact_id;
>group by f_lname, f_phone
>
>I end up with the following;
>Smith phone#1
>Smith phone#2
>Smith phone#3
>Smith phone#4
>
>I thought the group by clause would take care of this, but what i want to receive is
>Smith phone#1 phone#2 phone#3 phone#4
>
>I hope this makes sense. it feels like i'm missing something simple here, and while i could probably do this with multiple queries, i'm hoping there's an easier way. Is there? Thanks.
>
>Paul