>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
For numeric fields, you might use a sum() clause; for texts, that is not possible (AFAIK). You might create a UDF that returns all the phones for a PK value, and use this one in the SELECT statement.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)