Region Output knocks off SQL Query

Neil Hunter:

Hi everyone

I'm hoping someone can spot what is wrong with my SQL below as it works great until I outcome the Region description (t1.LONGDESCRIPTION). When I add this to the query below it duplicates some but not all records in the output.

I think there must be an error in my join but I can unot figure out what it is. 

I need to the region field in the output as I plan to use this as a parameter in Crystal Report so the end user can filter down the data by region.

Many thanks in advance for taking the time to look at this.

The query I'm using is:

 

 

 

select

 

 

 

act.ID,

rec.CONSTITUENT_ID,

rec.FIRST_NAME,

rec.LAST_NAME,

t3.LONGDESCRIPTION

from dbo.ACTIONS as act

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

inner join dbo.ActionAttributes as att on act.ID = att.PARENTID

inner join dbo.AttributeTypes as cat on att.ATTRIBUTETYPESID = cat.ATTRIBUTETYPESID

inner join dbo.CONSTIT_ADDRESS as conadd on rec.ID = conadd.CONSTIT_ID

inner join dbo.ADDRESS as add1 on conadd.ADDRESS_ID = add1.ID

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

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

inner join dbo.TABLEENTRIES as t3 on att.TABLEENTRIESID = t3.TABLEENTRIESID and t3.LONGDESCRIPTION = 'Financial Assessment'

where t2.LONGDESCRIPTION in ('Helpline Enquiry','Silverline Enquiry','Silverline Warm Transfer')

and cat.DESCRIPTION in ('Helpline - Non Residential: Needs', 'Helpline - Non Residential Care: Payment & Charges','Helpline - Non Residential Care: Care','Helpline - Non Residential Care: Complaints', 'Helpline - Non Residential Care: Carers')

and act.DTE between '2013-04-01' and '2014-03-31'

group by act.ID, rec.CONSTITUENT_ID,

rec.FIRST_NAME,

rec.LAST_NAME,

t3.LONGDESCRIPTION

order by rec.CONSTITUENT_ID

 

Hi Everyone Just to let you know i have figured what I have done wrong. I had not set a criteria to only select the records preferred address so when I used the Region it pulled through multiple times for records with more than one address recorded against them. Many thanks for looking at this anyway. Neil