cancel
Showing results for 
Search instead for 
Did you mean: 

Returning only 1 Row when join returns several

Former Member
2,574

I'm struggling work out how to a get a query to return 1 row of data not two.

I have two tables that look like this

tbl.Person
PersonID,   Name
1,      John Smith
2,      Paul Jones
3,      Greg Clarke
4,      Humpty Dumpty

tbl.Position
Personid,   EmployerID, status
1,      12911,      C
2,      11234,      A 
2,      33910,      C
3,      44561,      A
4,      66543,      Z

I'm doing this

select distinct Person.Name,
       (Case WHEN position.status = C THEN EmployerID ELSE Null END)
 FROM person
INNER JOIN Position on person.personid = position.personid
where status in ('A','C')

and what I would like to end up with is this

Name        EmployerID
John Smith  12911
Paul Jones  33910
Greg Clarke null

But of course what I actually get is this

Name        EmployerID
John Smith  12911
Paul Jones  33910
Paul Jones  null
Greg Clarke null

I understand why I am getting what I am getting, I just can't think of a better way to achieve what I want, Anyone got any ideas?

Accepted Solutions (0)

Answers (2)

Answers (2)

MarkCulp
Participant

Some clarification of your requirements is needed. It looks like you are wanting:

  • only employees that have either an A or a C status record in the Position table
  • for each employee selected, show the EmployerID value associated with the C record or null if there isn't one.

Is that correct. If so then try this:

create table person( PersonID int, Name varchar( 60 ) );

insert
  into Person( PersonID, Name ) 
values ( 1, 'John Smith' )
     , ( 2, 'Paul Jones' )
     , ( 3, 'Greg Clarke' )
     , ( 4, 'Humpty Dumpty' );

create table Position ( PersonID int, EmployerID int, Status char );

insert
  into Position( PersonID, EmployerID, Status )
values ( 1,      12911,      'C' )
     , ( 2,      11234,      'A' )
     , ( 2,      33910,      'C' )
     , ( 3,      44561,      'A' )
     , ( 4,      66543,      'Z' );

commit;

select dt.PersonID, p.EmployerID
  from ( select distinct Person.PersonID
           from Person
           join Position on Person.PersonID = Position.PersonID
          where Position.Status in ( 'A', 'C' ) ) dt
  left outer join Position p on p.PersonID = dt.PersonID
   and p.Status = 'C';

Note that the p.Status = 'C' predicate is part of the ON condition - if it was made part of the WHERE clause then the NULL row would be filtered out... making it part of the ON condition of the LEFT OUTER JOIN injects the appropriate NULL row.

HTH

Breck_Carter
Participant

Divide and conquer, anyone?

SELECT Person.Name,
       Position.EmployerID
  FROM Person
       INNER JOIN Position 
       ON Person.personid = Position.personid
 WHERE Position.status = 'C'
UNION
SELECT Person.Name,
       NULL
  FROM Person
       INNER JOIN Position 
       ON Person.personid = Position.personid
 WHERE Position.status = 'A'
   AND NOT EXISTS ( SELECT *
                      FROM Position
                     WHERE Person.personid = Position.personid
                       AND Position.status = 'C' );