I'm writing a SQL Server 2008 report that will compare genetic test results for animals. A genetic test consists of an animalId, a gene and a result. Not all animals will have the same genes tested but I need to be able to display the results side-by-side for a given set of animals and only include the genes that are present for at least one of the selected animals.
My TestResult table has the following data in it:
animalId gene result
1 a CC
1 b CT
1 d TT
2 a CT
2 b CT
2 c TT
3 a CT
3 b TT
3 c CC
3 d CC
3 e TT
I need to generate a result set that looks like the following. Note that Animal 3 is not being displayed (user doesn't want to see its results) and neither are results for Gene "e" since neither Animal 1 nor Animal 2 have a result for that gene:
SireID SireResult CalfID CalfResult Gene
1 CC 2 CT a
1 CT 2 CT b
1 NULL 2 TT c
1 TT 2 NULL d
But I can only manage to get this:
SireID SireResult CalfID CalfResult Gene
1 CC 2 CT a
1 CT 2 CT b
NULL NULL 2 TT c
1 TT NULL NULL d
This is the query I'm using.
SELECT
sire.animalId AS 'SireID'
,sire.result AS 'SireResult'
,calf.animalId AS 'CalfID'
,calf.result AS 'CalfResult'
,sire.gene AS 'Gene'
FROM
(SELECT
s.animalId
,s.result
,m1.gene
FROM
(SELECT [animalId ]
,result
,gene
FROM TestResult
WHERE animalId IN (1)) s
FULL JOIN
(SELECT DISTINCT
gene
FROM TestResult
WHERE animalId IN (1, 2)) m1
ON s.marker = m1.marker) sire
FULL JOIN
(SELECT
c.animalId
,c.result
,m2.gene
FROM
(SELECT animalId
,result
,gene
FROM TestResult
WHERE animalId IN (2)) c
FULL JOIN
(SELECT DISTINCT
gene
FROM TestResult
WHERE animalId IN (1, 2)) m2
ON c.gene = m2.gene) calf
ON
sire.gene = calf.gene
How do I get the SireIDs and CalfIDs to display their values when they don't have a record associated with a particular Gene? I was thinking of using COALESCE but I can't figure out how to specify the correct animalId to pass in. Any help would be appreciated.
