Getting single instance from when multiple desired records exist

[ghbeers]

I have a table PEOPLE, and each record in this table is left joined to another table XYZ that may or may not contain several records for each PEOPLE record. Multiple records related to the PEOPLE record may contain none, one or both of two desired codes, say A or B. So if A or B is found in any of the XYZ records, I want only a single instance returned. I have tried several approaches using EXISTS, SELECT 1, etc., but am still getting 2 instances returned for each PERSON record when both A and B are found in their XYZ records.

Any help on this will be greatly appreciated.

 

[gk53]

this is example It looks like what you need

declare @person table
(
	id int,
	name varchar(50)
)

declare @xyz table
(
	personid int,
	value varchar(50)
)

insert into @person select 1,'john'
insert into @person select 2,'mike'
insert into @person select 3,'jeff'
insert into @person select 4,'greg'

insert into @xyz select 1,'a'
insert into @xyz select 1,'b'
insert into @xyz select 2,'a'
insert into @xyz select 3,'a'
insert into @xyz select 3,'b'
insert into @xyz select 3,'c'
insert into @xyz select 4,'a'
insert into @xyz select 4,'b'
insert into @xyz select 4,'c'
insert into @xyz select 4,'d'



select p.*, x.recordsNumber
from @person p
	join (
	select personid, recordsNumber = COUNT(*)
	from @xyz
	group by personid) x
	on p.id = x.personid

 

[Olaf Doschke]

Actually exists is the right subquery, if you want to assure either code "a" or "b" is found and you have test data in @xyz not having "a" or "b" in it, too.

declare @person table
(
	id int,
	name varchar(50)
)

declare @xyz table
(
	personid int,
	code char(1)
)

insert into @person 
  values (1,'john')
  ,      (2,'mike')
  ,      (3,'jeff')
  ,      (4,'greg')

insert into @xyz 
   values (1,'a')
   ,      (1,'b')
   ,      (2,'a')
   ,      (3,'b')
   ,      (4,'c')

select * from @person p
where exists (Select 1 from @xyz where personid = p.id and code in ('a','b'))

For John code a and b exist, for mike only a, for jeff only b and for greg none, so greg is not in the result. All cases are tested.

Bye, Olaf.

 

[ghbeers]

Thank you for your quick responses. I tried the following using my real table records rather than the hypothetical ones:

select DISTINCT SA.SA_STUDENT_ID, X.RECORDNUMBER
FROM SA_2013 SA
left JOIN
(select SIF.IFAF_STUDENT_ID,SIF.IFAF_IMPORT_YEAR, SIF.IFAF_ISIR_TYPE, RECORDNUMBER = COUNT(*)
FROM ISIR_FAFSA SIF
GROUP BY SIF.IFAF_STUDENT_ID, SIF.IFAF_IMPORT_YEAR, SIF.IFAF_ISIR_TYPE) X
ON SA.SA_STUDENT_ID = X.IFAF_STUDENT_ID
AND X.IFAF_IMPORT_YEAR = '2013'
AND X.IFAF_ISIR_TYPE IN ('ISIR','CPSSG')

But I am getting multiple instances for a single student. I know why, but I do not know how to rearrange this to get only a single response if the student has at least one of the valid codes in the 2nd table. The join logic is among several others in the actual query I am trying to modify.

 

[ghbeers]

I resolved the issue by doing the following. Thank you both so much for refreshing my brain on something I've not done in quite a while.

<other declares and inserts>

DECLARE @ISIRFAFSA TABLE
(ID varchar(10))

INSERT INTO @ISIRFAFSA
SELECT SIF.IFAF_STUDENT_ID
FROM ISIR_FAFSA SIF
WHERE SIF.IFAF_IMPORT_YEAR = '2013'
AND SIF.IFAF_ISIR_TYPE IN ('ISIR','CPSSG')
GROUP BY SIF.IFAF_STUDENT_ID

<other stuff that included SIF.ID in the select statement>

select DISTINCT SA.SA_STUDENT_ID, SIF.ID
FROM SA_2013 SA
left JOIN @ISIRFAFSA SIF
ON SIF.ID = SA.SA_STUDENT_ID

<other stuff>

 

[Olaf Doschke]

If you want each studient just once, don't group by GROUP BY SIF.IFAF_STUDENT_ID, SIF.IFAF_IMPORT_YEAR, SIF.IFAF_ISIR_TYPE.

Since you have the IFAF_ISIR_TYPE in the group by you get a student having both 'ISIR' and 'CPSSG' type twice. Simply group by studientid and either take MIN(IFAF_ISIR_TYPE) or leave it out from the result.

Bye, Olaf.