Joining dbo.RECORDS and dbo.ADDRESS

Neil Hunter

 Hi Everyone

 I'm having problems with a query where I need to select information from the dbo.RECORDS table such as first name, last name etc.

 I also need to select the region field from dbo.ADDRESS but can not see how these 2 tables join? I initially used the ID field in both this gives the wrong address information. Below is the query I am using and any help would be appreciated:

 

 

select

 

 

act.DTE,

t1.LONGDESCRIPTION,

rec.CONSTITUENT_ID,

rec.FIRST_NAME,

rec.LAST_NAME

 

 

 

 

from dbo.ACTIONS as act

left join dbo.RECORDS as rec on act.RECORDS_ID = rec.ID

left join dbo.ADDRESS as add1 on rec.ID = add1.ID

left join dbo.TABLEENTRIES as t2 on act.TYPE = t2.TABLEENTRIESID

left join dbo.TABLEENTRIES as t1 on add1.REGION = t1.TABLEENTRIESID

 

 

 

 

where t2.LONGDESCRIPTION = 'Helpline Enquiry'

and act.DTE between '2013-09-01 00:00:00' and '2013-09-30 23:59:59'

 

Many thanks

 

Neil

 

 

Mark Zarbailov

Neil Hunter:

 Hi Everyone

 I'm having problems with a query where I need to select information from the dbo.RECORDS table such as first name, last name etc.

 I also need to select the region field from dbo.ADDRESS but can not see how these 2 tables join? I initially used the ID field in both this gives the wrong address information. Below is the query I am using and any help would be appreciated:

 

 

select

 

 

act.DTE,

t1.LONGDESCRIPTION,

rec.CONSTITUENT_ID,

rec.FIRST_NAME,

rec.LAST_NAME

 

 

 

 

from dbo.ACTIONS as act

left join dbo.RECORDS as rec on act.RECORDS_ID = rec.ID

left join dbo.ADDRESS as add1 on rec.ID = add1.ID

left join dbo.TABLEENTRIES as t2 on act.TYPE = t2.TABLEENTRIESID

left join dbo.TABLEENTRIES as t1 on add1.REGION = t1.TABLEENTRIESID

 

 

 

 

where t2.LONGDESCRIPTION = 'Helpline Enquiry'

and act.DTE between '2013-09-01 00:00:00' and '2013-09-30 23:59:59'

 

Many thanks

 

Neil

 

 

Use CONSTIT_ADDRESS table. Here is information from Raiser's Edge db schema help file:

Neil Hunter

Mark Zarbailov:
Use CONSTIT_ADDRESS table. Here is information from Raiser's Edge db schema help file:

 Thank you Mark, that worked a treat.