CODEWITHSUNDEEP
sqlsql-serversql-server-2008ms-access
techV
techV

Reputation: 935

Get the result based on column attribute value

I have a table like below :

Id   Plan  Attributes   Value
1     A      Name        AAA
2     A      Class       P
3     A      IsActive    True
4     B      Name        BBB
5     B      Class       Q
6     B      IsActive    False
7     C      Name        CCC
8     C      Class       R 
9     C      IsActive    True

I want to only get those Plan (with all their attributes) which have their IsActive attribute's value is set to True. I am not able to achieve this.

Desired Result :

  Id   Plan  Attributes   Value
    1     A      Name        AAA
    2     A      Class       P
    3     A      IsActive    True
    7     C      Name        CCC
    8     C      Class       R 
    9     C      IsActive    True

Upvotes: 1

Views: 603

Answers (5)

Shiju Shaji
Shiju Shaji

Reputation: 1730

Are you looking for this DEMO HERE

select * from @table_ 
where [plan] not in (select [plan] from @table_ where value ='False')

Upvotes: 0

Sergio Internicola
Sergio Internicola

Reputation: 347

First of all, this table is not normalized.

It should be like this:

Id Plan Name Class IsActive
 1   A  AAA   P    True
 2   B  BBB   Q    False
 3   C  CCC   R    True

The query for your un-normalized table is:

Select * 
From MyTable 
Where [Plan] In (
   Select [Plan] 
   From MyTable 
   Where Attributes = 'IsActive' And Value = 'True'
)

Note: I wrote [Plan] in square brackets because Plan is a reserved word.

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

You can try this:

EDIT:

select * from mytable 
where plan in (select plan from mytable 
    where Attributes = 'IsActive' and Value = 'True')

Upvotes: 1

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20499

This is what you're looking for:

SELECT *
FROM TABLE
WHERE PLAN IN (
        SELECT DISTINCT PLAN
        FROM TABLE
        WHERE Attributes = 'IsActive'
            AND Value = 'True');

The inner query will identify those plans which have the Attributes to IsActive and Value equal to True and the outer query will select all rows for those corresponding plans, which have been identified in the subquery.

Upvotes: 1

Praveen
Praveen

Reputation: 9345

Try

select * 
from tbl
where plan in (
    select plan from tbl 
    where Attributes = 'IsActive' and Value = 'True'
)

Upvotes: 1

Related Questions