Group By Query With LongVarChar Field Question

 

This part is solved, please see my question at the bottom of this topic

Hi, my problem is the following, doesn't seem too hard but it's been puzzling me for hours now, and haven't solved it yet, so I need your help!

I'm creating an application to read from an existing db running on SQL-server 2000. This db contains the tables:  tClient,  tCommunication and tAddress. All of the three tables share a field called nClient which is the unique key in tClient, but each client can have multiple adresses and communication (phone/email etc), so several nClient can occur with the same value in tCommunication and tAddress.

I want to run a query which searches for a text (%blah%) in several fields in any of the tables, but I don't want the query to return the same client more than once, which now does happen if a client has more than one address. Help me! I want to to either return the clients with only the address that contained the %blah% or only the first address it finds for the client. Please help!

Simplified version of my query is like this:

SELECT sLastname, sStreet, sCommunication FROM tClient
LEFT JOIN tCommunication ON tClient.nClient = tCommunication.nClient
LEFT JOIN tAddress ON tClient.nClient = tAddress.nClient
WHERE tClient.sLastname             LIKE '%blah%'
OR    tClient.sCompanyName          LIKE '%blah%'
OR    tClient.sClient               LIKE '%blah%'
OR    tClient.sFirstname            LIKE '%blah%'
OR    tClient.mMemo                 LIKE '%blah%'
OR    tCommunication.sCommunication LIKE '%blah%'
OR    tAddress.sStreet              LIKE '%blah%'
OR    tAddress.sNumber              LIKE '%blah%'
OR    tAddress.sZip                 LIKE '%blah%'
OR    tAddress.sCity                LIKE '%blah%';