Query table with multiple foreign keys to the same table

Question

ery table with multiple foreign keys to the same table?

I have tables that look something like this;

CREATE TABLE Patient
([pId] varchar(2), [name] varchar(20) );

INSERT INTO Patient
([pId] , [name])
VALUES
('p1', 'testp1'),
('p2', 'testp2'),
('p3', 'testp3');


CREATE TABLE Result
([resultId] varchar(2), [pid] varchar(2), [codeId1] varchar(2) , [codeId2] varchar(2), [codeId3] varchar(2));

INSERT INTO Result
([resultId], [pid], [codeId1], [codeId2], [codeId3])
VALUES
('r1', 'p1', 'c1', 'c2', 'c3'),
('r2', 'p2', 'c4', 'c5', 'c6'),
('r3', 'p3', 'c2', 'c5', null);

CREATE TABLE Code
([codeId] varchar(2), [name] varchar(20));

INSERT INTO Code
([codeId], [name])
VALUES
('c1', 'A'),
('c2', 'B'),
('c3', 'C'),
('c4', 'X'),
('c5', 'Y'),
('c6', 'Z');

And I want the result to look like this:

PID	NAME	CODEONE	CODETWO	CODETHREE
p1	testp1	A	B	C
p2	testp2	X	Y	Z
p3	testp3	B	Y	(null)

How do I write a query that will return the above results and perform well?

Answer 1

Try this:

select p.pId, p.name, c1.name as codeOne, c2.name as codeTwo, c3.name as codeThree
from Patient p
inner join Result r
on p.pId = r.pId
left outer join Code c1
on r.codeId1 = c1.codeId
left outer join Code c2
on r.codeId2 = c2.codeId
left outer join Code c3
on r.codeId3 = c3.codeId

You can try out this example and view the execution plan here:

http://sqlfiddle.com/#!6/f7b45/1