Ok, since nobody tried to answer, I did it myself < g >
Used two steps, though can use just one. I just thought, it would be quicker to do in 2.

>Hi everybody,
>
>I'd like to use one select statement for the following problem, but don't see a way so far.
>
>I have referrals (accounts) in Trans file with cTrans_pk as a primary key. These referrals are for patients, which have information in Patients table with cPatients_pk as PK. The name of the patient is stored in the Names table. One patient can have a primary name and some aliases (AKAs). If the patient has aliases, the iAlias_flag is set to 1, and cAlias_name_fk field holds the reference of the primary name (e.g. if the primary name is James Smith with the id of 1 and Jim Smith is an alias with the id 2, we would have:
>

>ID      Name          Alias_fk    iAlias_flag
>1       James Smith               1
>2       Jim Smith     1           ?  (not sure, I think, it's 0)

>
>Now I don't want to show the same account multiple times. E.g. I always want to show the primary name for each account. However, I should allow to search by AKAs as well and therefore sometimes I would have AKAs records returned only.
>
>====================================
>Bellow is my present code. DISTINCT keyword is enough for COUNT (first run), but it's not sufficient for actual data retrival. What do you think? Is my goal achievable?
>
>

>*---------------------- Location Section ------------------------
>*   Library: 	Asearchbiz.vcx
>*   Class: 		Searchobject
>*   Method: 	RunSearch()
>*----------------------- Usage Section --------------------------
>*)  Description:
>*)
>
>*   Scope:      Public
>*   Parameters:
>*$  Usage:
>*$
>*   Returns:
>*--------------------- Maintenance Section ----------------------
>*   Change Log:
>*       CREATED 	05/18/2005 - NN
>*		MODIFIED
>*----------------------------------------------------------------
>local loSelect, loDE, loCursor, lcSelectCMDFilter, lcCountSelectCMD, ;
>	lcBaseSelectCMD, lcCountSelectCMD, loCursorAdapter, lnReccount
>
>loDE = this.GetDataEnvironment()
>loCursor = m.loDE.GetCursorObject("ca_QuickSearch")
>loCursorAdapter = m.loCursor.oCursorAdapter
>lnReccount = 0
>this.nRecords = 0
>
>text TO lcCountSelectCMD NOSHOW PRETEXT 2
>	SELECT COUNT (distinct cTrans_pk) as cntRecs ;
>		FROM trans INNER JOIN Patients ;
>		ON Patients.cPatients_pk = Trans.cPatients_fk INNER JOIN names ;
>		ON Patients.cPatients_pk = Names.cPointer_fk
>ENDTEXT
>
>lcCountSelectCMD = m.lcCountSelectCMD + iif(empty(this.cJoinExpr),"", " " + this.cJoinExpr)
>lcCountSelectCMD = strtran(strtran(m.lcCountSelectCMD,";",[]), chr(13) + chr(10), [])
>
>text TO lcBaseSelectCMD NOSHOW PRETEXT 2
>	SELECT DISTINCT Trans.cTrans_pk AS ctrans_pk, ;
>		Trans.cClient_account_number, ;
>		Patients.dDob, ;
>		Trans.tAdmit_date AS tDOS, ;
>		PADR(NVL(ALLTRIM(Names.cl_name)+", ", "") + ;
>		NVL(ALLTRIM(Names.cf_name)+" ", "") + ;
>		NVL(Names.cm_initial, ""), 50) AS cpatient_name, ;
>		Product_lines.cProduct_line, ;
>		Trans.cCommissioned_Owner as cUserID,  ;
>        IIF(ISNULL(Trans.cResolution_Codes_fk) OR cResolution_Codes_Fk=="", ;
>        VisC.cCode_Description, VisCo.cCode_Description) as cStatus ;
>		FROM trans INNER JOIN Patients ;
>		ON Patients.cpatients_pk = Trans.cpatients_fk INNER JOIN names ;
>		ON Patients.cpatients_pk = Names.cpointer_fk ;
>		INNER JOIN product_lines ;
>		ON Product_lines.cproduct_lines_pk = Trans.cproduct_lines_fk ;
>		LEFT JOIN VisCodes VisC ON Trans.cStatus_codes_fk = VisC.cVisCodes_pk ;
>		LEFT JOIN VisCodes VisCo ON Trans.cResolution_codes_fk = VisCo.cVisCodes_pk
>ENDTEXT
>
>lcBaseSelectCMD = m.lcBaseSelectCMD + iif(empty(this.cJoinExpr),"", " " + this.cJoinExpr)
>lcBaseSelectCMD = strtran(strtran(m.lcBaseSelectCMD,";",[]), chr(13) + chr(10), [])
>
>lcSelectCMDFilter = this.cWhereExpr
>
>with this
>	.lRepeatRequery = .f.
>
>** These commands should change CursorAdapter's SelectCMD
>	loCursorAdapter.cBaseSelectCmd = m.lcCountSelectCMD
>	loCursorAdapter.cSelectCmdFilter = m.lcSelectCMDFilter
>
>	_cliptext = loCursorAdapter.selectcmd
>	local lnRequeryResult
>	lnRequeryResult = .requery(.t., "ca_QuickSearch")
>
>	do while m.loCursorAdapter.lRepeatRequery = .t. and m.lnRequeryResult <> REQUERY_SUCCESS && the user tried to stop it
>		lnRequeryResult = .requery(.t., "ca_QuickSearch")
>		loCursorAdapter.lRepeatRequery = .f.
>	enddo
>
>	if vartype(m.plStop) = "L" and m.plStop = .t.
>		lnReccount = 0
>	else
>		lnReccount = ca_QuickSearch.cntRecs
>		.nRecords = m.lnReccount
>		if .nRecords = 0
>			.lNoRecordsSelected = .t.
>		endif
>	endif
>	loCursorAdapter.cBaseSelectCmd = m.lcBaseSelectCMD
>	if  .nRecords > 0 and .nRecords <= .nMaxRecordsToDisplay
>		lnReccount = .CreateFinalResult()
>	endif
>endwith
>
>return m.lnReccount
>