Query question

[llmclaughlin]

With the below query, say if resulnameid = 112 doesn't exists in the results_details table but all others do, the query returns nothing.

How do you solve this?


SELECT R101.resultvalue AS R101,
R102.R102,
R103.R103,
R104.R104,
R105.R105,
R107.R107,
R109.R109,
R110.R110,
R111.R111,
R112.R112,
R113.R113,
R114.R114,
R116.R116,
R117.R117,
R119.R119,
R120.R120,
R121.R121,
R122.R122,
R123.R123,
R124.R124,
R126.R126,
R127.R127,
R129.R129,
R130.R130,
R132.R132,
R133.R133,
R135.R135,
R136.R136,
R137.R137,
R138.R138,
R139.R139,
R140.R140,
R141.R141,
R142.R142,
R143.R143,
R144.R144
FROM (SELECT RESULTVALUE AS R102
FROM RESULTS_DETAILS
WHERE ResultNameID = 102 AND ResultID = 1413528
GROUP BY resultvalue) R102,
(SELECT RESULTVALUE AS R103
FROM RESULTS_DETAILS
WHERE ResultNameID = 103 AND ResultID = 1413528
GROUP BY resultvalue) R103,
(SELECT RESULTVALUE AS R104
FROM RESULTS_DETAILS
WHERE ResultNameID = 104 AND ResultID = 1413528
GROUP BY resultvalue) R104,
(SELECT RESULTVALUE AS R105
FROM RESULTS_DETAILS
WHERE ResultNameID = 105 AND ResultID = 1413528
GROUP BY resultvalue) R105,
(SELECT RESULTVALUE AS R107
FROM RESULTS_DETAILS
WHERE ResultNameID = 107 AND ResultID = 1413528
GROUP BY resultvalue) R107,
(SELECT RESULTVALUE AS R109
FROM RESULTS_DETAILS
WHERE ResultNameID = 109 AND ResultID = 1413528
GROUP BY resultvalue) R109,
(SELECT RESULTVALUE AS R110
FROM RESULTS_DETAILS
WHERE ResultNameID = 110 AND ResultID = 1413528
GROUP BY resultvalue) R110,
(SELECT RESULTVALUE AS R111
FROM RESULTS_DETAILS
WHERE ResultNameID = 111 AND ResultID = 1413528
GROUP BY resultvalue) R111,
(SELECT RESULTVALUE AS R112
FROM RESULTS_DETAILS
WHERE ResultNameID = 112 AND ResultID = 1413528
GROUP BY resultvalue) R112,
(SELECT RESULTVALUE AS R113
FROM RESULTS_DETAILS
WHERE ResultNameID = 113 AND ResultID = 1413528
GROUP BY resultvalue) R113,
(SELECT RESULTVALUE AS R114
FROM RESULTS_DETAILS
WHERE ResultNameID = 114 AND ResultID = 1413528
GROUP BY resultvalue) R114,
(SELECT RESULTVALUE AS R116
FROM RESULTS_DETAILS
WHERE ResultNameID = 116 AND ResultID = 1413528
GROUP BY resultvalue) R116,
(SELECT RESULTVALUE AS R117
FROM RESULTS_DETAILS
WHERE ResultNameID = 117 AND ResultID = 1413528
GROUP BY resultvalue) R117,
(SELECT RESULTVALUE AS R119
FROM RESULTS_DETAILS
WHERE ResultNameID = 119 AND ResultID = 1413528
GROUP BY resultvalue) R119,
(SELECT RESULTVALUE AS R120
FROM RESULTS_DETAILS
WHERE ResultNameID = 120 AND ResultID = 1413528
GROUP BY resultvalue) R120,
(SELECT RESULTVALUE AS R121
FROM RESULTS_DETAILS
WHERE ResultNameID = 121 AND ResultID = 1413528
GROUP BY resultvalue) R121,
(SELECT RESULTVALUE AS R122
FROM RESULTS_DETAILS
WHERE ResultNameID = 122 AND ResultID = 1413528
GROUP BY resultvalue) R122,
(SELECT RESULTVALUE AS R123
FROM RESULTS_DETAILS
WHERE ResultNameID = 123 AND ResultID = 1413528
GROUP BY resultvalue) R123,
(SELECT RESULTVALUE AS R124
FROM RESULTS_DETAILS
WHERE ResultNameID = 124 AND ResultID = 1413528
GROUP BY resultvalue) R124,
(SELECT RESULTVALUE AS R126
FROM RESULTS_DETAILS
WHERE ResultNameID = 126 AND ResultID = 1413528
GROUP BY resultvalue) R126,
(SELECT RESULTVALUE AS R127
FROM RESULTS_DETAILS
WHERE ResultNameID = 127 AND ResultID = 1413528
GROUP BY resultvalue) R127,
(SELECT RESULTVALUE AS R129
FROM RESULTS_DETAILS
WHERE ResultNameID = 129 AND ResultID = 1413528
GROUP BY resultvalue) R129,
(SELECT RESULTVALUE AS R130
FROM RESULTS_DETAILS
WHERE ResultNameID = 130 AND ResultID = 1413528
GROUP BY resultvalue) R130,
(SELECT RESULTVALUE AS R132
FROM RESULTS_DETAILS
WHERE ResultNameID = 132 AND ResultID = 1413528
GROUP BY resultvalue) R132,
(SELECT RESULTVALUE AS R133
FROM RESULTS_DETAILS
WHERE ResultNameID = 133 AND ResultID = 1413528
GROUP BY resultvalue) R133,
(SELECT RESULTVALUE AS R135
FROM RESULTS_DETAILS
WHERE ResultNameID = 135 AND ResultID = 1413528
GROUP BY resultvalue) R135,
(SELECT RESULTVALUE AS R136
FROM RESULTS_DETAILS
WHERE ResultNameID = 136 AND ResultID = 1413528
GROUP BY resultvalue) R136,
(SELECT RESULTVALUE AS R137
FROM RESULTS_DETAILS
WHERE ResultNameID = 137 AND ResultID = 1413528
GROUP BY resultvalue) R137,
(SELECT RESULTVALUE AS R138
FROM RESULTS_DETAILS
WHERE ResultNameID = 138 AND ResultID = 1413528
GROUP BY resultvalue) R138,
(SELECT RESULTVALUE AS R139
FROM RESULTS_DETAILS
WHERE ResultNameID = 139 AND ResultID = 1413528
GROUP BY resultvalue) R139,
(SELECT RESULTVALUE AS R140
FROM RESULTS_DETAILS
WHERE ResultNameID = 140 AND ResultID = 1413528
GROUP BY resultvalue) R140,
(SELECT RESULTVALUE AS R141
FROM RESULTS_DETAILS
WHERE ResultNameID = 141 AND ResultID = 1413528
GROUP BY resultvalue) R141,
(SELECT RESULTVALUE AS R142
FROM RESULTS_DETAILS
WHERE ResultNameID = 142 AND ResultID = 1413528
GROUP BY resultvalue) R142,
(SELECT RESULTVALUE AS R143
FROM RESULTS_DETAILS
WHERE ResultNameID = 143 AND ResultID = 1413528
GROUP BY resultvalue) R143,
(SELECT RESULTVALUE AS R144
FROM RESULTS_DETAILS
WHERE ResultNameID = 144 AND ResultID = 1413528
GROUP BY resultvalue) R144,
results_details R101
WHERE r101.ResultNameID = 101 AND R101.ResultID = 1413528
GROUP BY r101.resultvalue;

Louie

 

[SimonSellick]

You could try something like:

select
  r1.r
, r2.r
, r3.r
from (select a r from a where a = 1 group by a) r1
  left join (select a r from a where a = 2 group by a) r2 on 1 = 1
  left join (select a r from a where a = 3 group by a) r3 on 1 = 1

That should return a NULL value in the column instead of stopping the query returning a result at all.