Phsample missing where Pedon labsample number populated

[jneme910]

BASE TABLE pedon.

EXEC SQL SELECT TOP 100 peiid/1 AS peiid, 1 AS id
FROM pedon
INNER JOIN phorizon ON phorizon.peiidref=pedon.peiid AND pedlabsampnum IS NOT NULL 
AND peiid IN (SELECT p1.peiid
FROM pedon AS p1 
INNER JOIN phorizon AS ph1 ON ph1.peiidref=p1.peiid AND pedlabsampnum IS NOT NULL
AND p1.peiid 
IN        
(9635, 9637, 9645, 9657, 9677, 9679, 9684, 9897, 9898, 20617, 27121, 27131, 27132, 27140, 27142, 27147, 27153, 49057, 49058, 49059, 49060, 49061, 50744, 50745, 50746, 50754, 50751, 50753, 55222, 63494, 64427, 64430, 64431, 64433, 64435, 64439, 64482, 64488, 64489, 64490, 64491, 64492, 64502, 64503, 64506, 98036, 297972, 66406, 66409, 67480, 67655, 67657, 67658, 67659, 67660, 67661, 98149, 98157, 98176, 99159, 99176, 99308, 99261, 99290, 99294, 99254, 99324, 99557, 99694, 99790, 64426, 108352, 108354, 108355, 108358, 108359, 108360, 108361, 108362, 108363, 49314, 111792, 111794, 1181334, 111807, 111840, 111842, 111846, 111860, 111868, 111870, 112669, 112794, 112827, 112884, 112942, 113179, 114082, 114035, 116114, 43391, 63788, 63789, 64602, 168290, 168170, 168177, 186624, 1176494, 831992, 951031, 167545, 167844, 167932, 168133, 168135, 168138, 168156, 168168, 168255, 168272, 195471, 220149, 235271, 242215, 253218, 254008, 254479, 254480, 254481, 254482, 254521, 256258, 256259, 261275, 346409, 361082, 713703, 361864, 361865, 361866, 361867, 361869, 361870, 361871, 361872, 361873, 362463, 362564, 364287, 364372, 364579, 364580, 364585, 364643, 364651, 364656, 364271, 364821, 364824, 365476, 366579, 366580, 366581, 366582, 366583, 366584, 366585, 366586, 366587, 366588, 366595, 366596, 371012, 371013, 371014, 371015, 371016, 371017, 371018, 371019, 371020, 371021, 371572, 371573, 371574, 371575, 371576, 371577, 365297, 365298, 365299, 338020, 351623, 261847, 352512, 352514, 365289, 365291, 366597,47811, 47812, 66852, 204228, 204236, 204233, 204234, 204226, 113829, 1072595, 1072597, 863924, 784495, 181718, 773877, 176352, 196353, 217739, 220530, 234969, 242112, 247621, 247724, 247882, 247894, 248563, 297788, 297824, 297831, 297833, 375637, 388229, 459643, 1017200, 598189, 662781, 788182, 799737, 799738, 799739, 824113, 825699, 825703, 833633, 842394, 879094, 878494, 885717, 885731, 885749, 885751, 885752, 885753, 885754, 903894, 903897, 903912, 833644, 668953, 10104, 10116, 10118, 10367, 10368, 10369, 10370, 10371, 10606, 10607, 10608, 10609, 10610, 10611, 10892, 10893, 10894, 27562, 46805, 46807, 46810, 46811, 46812, 46813, 46814, 46815, 46838, 46840, 46860, 46861, 46862, 46863, 46864, 46867, 46870, 46871, 46880, 46882, 46883, 46887, 46888, 46889, 46890, 46920, 46921, 46922, 46945, 46952, 46953, 46954, 46986, 46987, 46988, 46996, 46997, 47013, 47014, 47015, 47016, 47017, 47018, 47019, 47020, 47021, 47034, 47035, 47038, 47039, 47040, 47041, 47048, 47049, 47064, 47065, 47079, 50008, 51228, 51233, 56013, 56018, 56021, 56022, 56032, 56039, 56041, 56055, 56134, 56139, 56140, 56141, 56143, 56149, 56162, 67296, 67297, 67298, 67300, 67329, 67330, 67331, 9631, 19781, 30684, 30685, 34755, 34756, 34757, 34758, 34759, 34760, 34761, 34762, 34763, 34764, 36643, 44687, 44697, 44699, 44800, 49115, 49116, 50074, 50076, 50077, 50592, 50594, 50596, 50599, 50602, 50644, 51956, 52148, 52149, 810748, 52943, 63454, 63549, 63551, 66314, 66357, 66358, 66359, 66360, 66361, 67581, 67582, 67583, 67584, 67601, 67603, 67604, 88687, 79543, 65735, 65734, 65736, 65737, 50647, 115906, 133159, 133928, 133932, 146043, 73277, 161907, 164081, 164101, 164174, 164230, 165828, 50517, 180996, 186657, 186658, 186659, 192532, 192547, 192601, 195195, 219104, 220107, 220194, 220523, 220560, 240385, 253401, 266232, 294817, 774201, 313272, 313379, 313654, 313791, 313859, 313907, 850222, 339716, 1079962, 622689, 610101)


LEFT OUTER JOIN phsample AS phs1 ON phs1.phiidref=ph1.phiid GROUP BY p1.peiid
HAVING COUNT (phs1.phiidref) = 0  AND p1.peiid=pedon.peiid )GROUP BY peiid;
AGGREGATE ROWS BY id COLUMN peiid LIST ', '.