CODEWITHSUNDEEP
sqlunion
Ash Pimento
Ash Pimento

Reputation: 11

Show missing data and list with existing data

I have 2 tables a table of PARENT ACCOUNTS (holds PARENT ID and CHILD ID) a table of CHILD ACCOUNTS (Holds CHILD ID and ATTRIBUTE)

1 PARENT can have many CHILDREN

Each Child has one or more attributes, and each child must have the same as the other in the parent group

so if Parent 1 has Child1, Child2, Child3

Child1, Child2, Child3 MUST have the same attribute - say they are BLUE

If Child3 is missing this attribute (doesn't exist) then I need to show All the Child accounts and highlight which one is the missing one.

select Child, Parent,Attribute
from 
  [dbo].[AttributeTable] as AttReq
   LEFT OUTER JOIN
  [dbo].[ChildDetails] ChildDetails

 ON AttReq.Child = ChildDetails.Child
 where Parent = 'Parent1'
CHILD ACOUNT PARENT ACCOUNT ATTRIBUTE
Child1 Parent1 Attribute 1
Child1 Parent1 Attribute 2
Child1 Parent1 Attribute 3
Child2 Parent1 Attribute 1
Child2 Parent1 Attribute 4
Child2 Parent1 Attribute 2
Child2 Parent1 Attribute 3
Child3 Parent1 Attribute 1
Child3 Parent1 Attribute 2
Child3 Parent1 Attribute 3

So this one, only Child 2 has attribute 4.

I want to list all the Child accounts that are missing 4 - and which one HAS 4. I don't need to show which ones they all have.

CHILD ACOUNT PARENT ACCOUNT ATTRIBUTE
Child1 Parent1 Missing Attribute 4
Child2 Parent1 Missing Attribute 4
Child3 Parent1 Has Attribute 4

Upvotes: 1

Views: 39

Answers (1)

Clifford Piehl
Clifford Piehl

Reputation: 535

If you are evaluating the instances of an attribute across several attributes, you need a sub-query to evaluate at a higher level of analysis.

I would probably do this with a case statement that sums the instances of the attribute you are looking for across each child.

To take your query as the sub-query:

SELECT SUM(CASE WHEN ATTRIBUTE = 'Attribute 4' THEN 1 ELSE 0 END) [Attribute Count]
    ,Child
    ,Parent
FROM(
SELECT Child
    ,Parent
    ,Attribute
FROM [dbo].[AttributeTable] as AttReq
    LEFT OUTER JOIN [dbo].[ChildDetails] ChildDetails
        ON AttReq.Child = ChildDetails.Child
WHERE Parent = 'Parent1'
) Subquery
GROUP BY Child
    ,Parent

A child with [Attribute Count] more than 0 have the attribute. Those with 0 do not have the attribute.

Upvotes: 0

Related Questions