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?