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?
Some clarification of your requirements is needed. It looks like you are wanting:
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.
answered 11 Sep '13, 15:30
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' );
answered 11 Sep '13, 16:52