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?

asked 11 Sep '13, 13:01

Ian%20Morris's gravatar image

Ian Morris
31113
accept rate: 0%

edited 11 Sep '13, 13:14

Mark%20Culp's gravatar image

Mark Culp
22.5k9129264

What rule does exist that tells for PersonID 2, you'd expect the row with status 'C' and not with status 'A'?

(BTW, that's a really well-stated question, I'd say - thanks :)

(11 Sep '13, 15:10) Volker Barth

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

permanent link

answered 11 Sep '13, 15:30

Mark%20Culp's gravatar image

Mark Culp
22.5k9129264
accept rate: 40%

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' );
permanent link

answered 11 Sep '13, 16:52

Breck%20Carter's gravatar image

Breck Carter
26.8k420580826
accept rate: 20%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×21
×7
×5

question asked: 11 Sep '13, 13:01

question was seen: 775 times

last updated: 11 Sep '13, 16:52