|
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. HTH |
|
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' );
|
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 :)