>>>>I have a clients table and a locations table. Each client has and address that is in the locations table. I want to join the clients table to the locations table so I can replace the address in the clients table with the key to that address in the locations table.
>>>>
>>>>For the most part, there is only one of each address in the locations table. But because I'm working with bad data, there might be more than one.
>>>>
>>>>Is there a join that will take one and only one of each client and match it up to the first address in locations that it finds, and doesn't make a second entry of the same client for the second address in locations?
>>>>
>>>
>>>If you don't mind doing a two stage query.
>>>
>>>SELECT * FROM locations GROUP BY id GROUP BY ID INTO CURSOR locations2
>>>SELECT clients.*, locations2.address FROM clients, locations WHERE clients.id = locations2.id INTO CURSOR clientloc
>>>
>>>ID is the unique key between the two tables.
>>
>>I don't think that'll do it. You're joining on clientid. There's definitely a one to many relationship there. I need to find the client's address in the list of locations.
>>
>
>Maybe I am missing the point. I though you had the following... (very much simplified)
>
>CLIENT TABLE
>ID NAME
>1 BILL
>2 JOE
>3 MICHELLE
>
>LOCATIONS TABLE
>ID ADDRESSS
>1 123 MAIN
>1 2 GRANVILLE
>2 432 WALL
>3 982 APPLE
>
>and that you wanted to join only one of the addresses to the clients.

Maybe I'm not being clear. Currently, the clients table and the trips table contain the whole address of home, pickup, and dropoff. I took all the addresses from both tables and put them into one table and gave them a key. Now I want to put that key back into the clients and trips table instead of the whole address.

Thanks,

-Michelle