I need a subquery help

1

For the last column here the patient collaterals I want to combine the last column cv.full_name as there will be multiple ollaterals for the patient.
I want to have a subquery that will take each collateral and add a column to the data set: I'm not sure where to put this (select sq.full_name from colateral_view sq as Collateral1)
But how do I loop this so that I get all of the patients collaterals?

select distinct ac.first_name, ac.last_name, ac.id_no, ac.dob, st.description, co.description,
rd.race, rd.ethnicity_description, pe.program_name, ih.income_amount, cv.full_name from address ad
inner join all_clients_view ac
on ad.people_id = ac.people_id
inner join county co
on ad.county = co.county_id
inner join state st
on co.state_id = st.state_id
inner join rpt_demographics rd
on ad.people_id = rd.people_id
right join program_enrollment_expanded_view pe
on pe.people_id = ac.people_id
inner join income_history_view ih
on ac.people_id = ih.people_id
right join colaterals_view cv
on ad.people_id = cv.people_id
where ad.is_active = 1 and cv.event_name in('Personal Collateral', 'Family')

2

Depending on the version of sql-server you can use string agg. Perhaps you can create an extra view with only people_id and 1 extra column for all the colaterals.

STRING_AGG (Transact-SQL) - SQL Server | Microsoft Docs

stackoverflow.com

3

based on this you can right join to a subquery that does this

image

declare @colaterals table ( full_name  varchar(50))

insert into @colaterals
select 'Vaydher' union
select 'Princess Lead' union
select 'Look'


select STRING_AGG(CONVERT(NVARCHAR(max), ISNULL(full_name,'N/A')), ',') AS csv 
  from @colaterals
4

I can get the exact duplicates with this query but is there a way to tweak this so that I can get the rows that have the same last name, first name and id but the race info, is different > that means they entered 2 race records for the same person. The query gives me the people they entered the same race info twice.

select ra.people_id, ra.race_info_id, ac.last_name, ac.first_name, ac.id_no, count( ) from race ra join all_clients_view ac on ra.people_id = ac.people_id group by ra.people_id, ra.race_info_id, ac.last_name, ac.first_name, ac.id_no having count( )

1 this gives only the exact matches. I have some cases where the race is entered twice for a client. Not exact match. I have to locate those as well. For bi-racial we have a value for bi-racial but some enter 2 anyway. I need to locate the race where it's not exactly the same

5

Totally new requirement than your original question right?