Return SQL Result with another field if it is not null

You want to LEFT JOIN that table and move the where condition up to the JOIN like this:

SELECT ARPayment.amount,ISNULL(LastName, '') + ', ' + ISNULL(FirstName, '') AS PatientName, 
ISNULL(PatAdd.Address1, '') + ', ' + ISNULL(PatAdd.Address2, '') AS AddLine1, 
ISNULL(PatAdd.City, '') + ', ' + ISNULL(PatAdd.Prov, '') + ', ' + ISNULL(PatAdd.Postal, '')AS AddLine2, 
ARPayment.[Date] AS PaymentDate, PatAdd.city, PatAdd.Prov, PatAdd.Postal 
FROM ARPayment 
INNER JOIN AR ON (ARPayment.ARID = AR.ID) 
INNER JOIN Pat ON (AR.BillToPatID = Pat.ID) 
LEFT JOIN PatAdd ON (Pat.ID = PatAdd.PatID) AND (Pat.ID IN (26928)and PatAdd.UseForBilling = 1)
WHERE (ARPayment.[Date] >= '2009-01-01 00:00:00') 
  AND (ARPayment.[Date] <  '2010-01-01 00:00:00') 
  AND (Type = 0)

Select allOpen in new window

I tried pasting that into SSMS and it returned way too many results (over 31,000).  I'm not sure if I have explained it clearly enough.

For every patient I may have one or many address stored.  If the patient only has one address it is stored in the PAT table in the ADDRESS1 field.  If the patient has more than 1 address then the additional address (2nd 3rd 4th etc.) address' are stored in the PATADD table in the ADDRESS1 field.  If a patient only has one address then I want it to pull from Pat.Address1.  If a patient has multiple address but none of the additonal address' are marked as a billing address (ie PATADD.BillToPatID = 0 or NULL) then I still want to use Pat.Address1.  If one of the additional address' are marked as a billing address (ie PATADD.BillToPatID = 1) then I want to use PatAdd.Address1.

I hope that makes sense.

Hi tsmit877,

I have looked into your problem & I believe that this can be resolved easily with help of one user define SQL function which return varchar value.

If you can send me the table structure then I would like to help you build that function too.

Function will be take the Pat.ID as input parameter & return the one Address based on the criteria you have mentioned above.

Also somehow i believe that below part of your SQL statement is need to be in that function

-----------------------
ISNULL(PatAdd.Address1, '') + ', ' + ISNULL(PatAdd.Address2, '') AS AddLine1,
ISNULL(PatAdd.City, '') + ', ' + ISNULL(PatAdd.Prov, '') + ', ' + ISNULL(PatAdd.Postal, '')AS AddLine2,

------------------

mail me the DB structure here - nishchaya.andrew@gmail.com

hope that above statement helped you....

Cheers.

Please post the structure of your tables and indicate the primary keys.

>>mail me the DB structure here - nishchaya.andrew@gmail.com <<
Please do not do that.  That is against EE Guidelines.

Please post a message in Community Support to have your email address removed.

Also, in your original question you stated:
"I would like the query to return Pat.Address1 if there aren't any records where PatAdd.UseForBilling = 1"
Now you mention:
"If one of the additional address' are marked as a billing address (ie PATADD.BillToPatID = 1) then I want to use PatAdd.Address1."
So which is it?  Both conditions?

hi acperkins,

My apologizes & thanks to you reminding me  on below.

>>mail me the DB structure here - nishchaya.andrew@gmail.com <<

I will keep thin in my mind & will not happen again 4m my side.

So tsmit877 kindly post the structure here only...

thanks.

Thanks, that works perfectly!