The forum will be down for maintenance at some point between Friday, September 25, 2020 at 5pm PDT and Sunday, September 27, 2020 at 11:59 PDT. Downtime is unknown but will be minimized.

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

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

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
accept rate: 0%

edited 11 Sep '13, 13:14

Mark%20Culp's gravatar image

Mark Culp

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 ) );

  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 );

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


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.


permanent link

answered 11 Sep '13, 15:30

Mark%20Culp's gravatar image

Mark Culp
accept rate: 40%

Divide and conquer, anyone?

SELECT Person.Name,
  FROM Person
       INNER JOIN Position 
       ON Person.personid = Position.personid
 WHERE Position.status = 'C'
SELECT Person.Name,
  FROM Person
       INNER JOIN Position 
       ON Person.personid = Position.personid
 WHERE Position.status = 'A'
                      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
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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 11 Sep '13, 13:01

question was seen: 1,522 times

last updated: 11 Sep '13, 16:52