1

I have a collection of parent items and each has an indeterminate set of child items. Each child item as an attribute that is X or NULL. (Apologies for the odd Boolean structure, but that's what I have to work with.)

Parent  Child  Attribute
------  -----  ---------
  A       1       X
  A       2       X
  A       3      NULL

  B       1       X
  B       2      NULL
  B       3      NULL

  C       1       X
  C       2       X

  D       1      NULL

  E       1      NULL
  E       2      NULL

I want to identify parent items as True or False based on the child items. One NULL value will result in a False return for that parent item. Here are the desired results:

  A       False
  B       False
  C       True
  D       False
  E       False

The ideal solution would even handle a parent that has no child records (result is False).

I can do this using temporary tables. That solution is fairly long and I don't think presenting it would add any value to this post.

How can I do this with an SQL query using no temporary tables?

I would present "things I've tried" but I don't know enough how to even start.

Smandoli
  • 6,919
  • 3
  • 49
  • 83

2 Answers2

4

I don't think this is equivalent to nor. You just want to know if any values are NULL -- or equivalently if all values are 'X'. Use case and aggregation:

select parent,
       (case when count(*) = count(attribute) then 'true'
             else 'false'
        end)
from t
group by parent;

To handle a parent with no children requires a list of parents, separate from this list:

select p.parent,
       (case when count(*) = count(t.attribute) and count(t.parent) > 0 then 'true'
             else 'false'
        end)
from parents p left join
     t
     on p.parent = t.parent
group by p.parent;

As a reminder, count(*) counts the number of rows in the result set. COUNT() with an expression (including a column) counts the number of non-NULL values. When these are the same, there are no non-NULL values.

Smandoli
  • 6,919
  • 3
  • 49
  • 83
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

You can do this via aggregates..,when no attributes,i have kept the column as blank..

In summary assign true or false to each row and get min value

create table #test
(
id int,
name char(2)
)

insert into #test

select 1,'x'
union all
select 1,null
union all
select 2,null
union all
select 3,''

with cte
as
(
select  id,b.*
from #test t1
cross apply
(
select case when name is null or name='' then 'False' Else 'True' end as 'chk' from #test t2 where t2.id=t1.id ) b
)
select id,min(chk)
from
cte group by id

You can remove even cross apply:

with cte
as
(
select  id,case when name is null or name='' then 'False' Else 'True' end as 'chk'
from #test t1
)
select id,min(chk)
from
cte group by id

Output: 

1   False
2   False
3   False
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • This uses a temp table as part of a stored procedure or possibly a view. As I said, I want to handle it in a query. – Smandoli Mar 20 '16 at 18:14
  • I didn't get the handle it in a query part.does this mean in a single select – TheGameiswar Mar 20 '16 at 18:17
  • It means using an ad-hoc query instead of a procedural approach. See http://stackoverflow.com/a/22970/122139. This doesn't mean using a single SELECT -- it's possibly to nest queries, or use UNION; these easily allow multiple SELECTS. It *does* mean not using a temp table. – Smandoli Mar 20 '16 at 18:53
  • Correction -- every solution here uses an ad-hoc query, so that's not the issue. I don't want any temporary tables -- that's the issue. Sorry for the wrong concepts. – Smandoli Mar 21 '16 at 13:06