Ok here comes another SQL query, as you can see in my code below im using the ‘IS NULL’ code but it still brings back data that contain values in that field??? Highlighted below:-
SELECT DISTINCT d.name, d2.name as Parent_Folder, d.dataid AS dataid, d6.name as Structure, i.valstr
FROM dtree d
INNER JOIN LlAttrData f ON (d.DataID = f.ID AND f.attrid = 6 AND f.DefID = 4208)
INNER JOIN LlAttrData g ON (d.DataID = g.ID AND g.attrid = 4 AND g.DefID = 4208)
INNER JOIN LlAttrData h ON (d.DataID = h.ID AND h.attrid = 5 AND h.DefID = 4208)
INNER JOIN LlAttrData i ON (d.DataID = i.ID AND i.attrid = 2 AND i.DefID = 4208)
Inner join dtree d2 on d.parentid = d2.dataid
Inner join dtree d3 on d2.parentid = d3.dataid
Inner join dtree d4 on d3.parentid = d4.dataid
Inner join dtree d5 on d4.parentid = d5.dataid
Inner join dtree d6 on d5.parentid = d6.dataid
Where d.subtype = 0
and (
d.name IN ('01 Demographic Information','02 Assessment','03 Family Support','04 CIN Planning & Review','05 Child Protection Investigation & Review','06 CLA Care Planning & Review','07 Pre-Adoption','08 Correspondence & Minutes','09 Finance ','10 Legal','11 Third Party Information','12 Occupational Therapist','13 Non File Plan Documents','14 SIAT Documents')
and d2.name = '02 Social Care'
)
OR (
d.name IN ('01 Demographic Information','02 Assessments','03 Correspondence','04 Accommodation','05 Education / Employment / Training','06 Health','07 Finance','08 Non File Plan Documents')
and d2.name = '03 After Care'
)
and d2.name != '01 Early Intervention & Prevention'
AND f.valstr = 'Child'
AND g.valint IS NULL
AND h.valstr IS NOT NULL
AND (i.valstr IS NULL)
ORDER BY d.dataid
