Thanks, that's what I was looking for.

AWS

.... after a few more minutes on the topic I think I simply going to filter the ADO recordset. Seems to be the easiest and cleanest.

But I...

Alrighty, got it figured out.

Here is the final code:

SELECT

pp.PatientProfileID,

COALESCE(pc.[First] + ' ' + pc.[Last] + ' ('+ UPPER(pc.ContactRelToPatient) + ')','') AS [Emergency Contact]

FROM

dbo.PatientProfile pp

LEFT OUTER JOIN

(

SELECT

PatientProfileId,

COALESCE(

MIN(CASE WHEN UPPER(ContactRelToPatient) =...

Thanks Sean for the heads up. I'll follow through with the link in your sig for finding more answers.

EDIT:

PatientProfile table:

--===== If the test table already exists, drop it

...

I've managed to find one error. On my "second" ON (join section) I had:

ON

(pc2.PatientProfileId = pc.PatientProfileId)

and it needs to be:

ON

(pc2.EContact = pc.PatientProfileId)

which now yields:

PatientId | Emergency Contact

8144 | <null>

Thanks, that does it. There was a "TEST" user in the empMain table that when I did that before I would get a conversion error converting "TEST" to int...

Here is db1.ERate table:

***********************************

CREATE TABLE [dbo].[ERate](

[co] [varchar](10) NOT NULL,

[id] [varchar](10) NOT NULL,

[rateCode] [varchar](10) NOT NULL,

[salary] [decimal](14, 2) NULL,

[rate] [decimal](14, 6) NULL,

[ratePer] [varchar](10) NULL,

... ETC. Rest of fields not necessary...