1

I have below data in my ColorAssign table;

| PKID | Name    | Type | HasAssigned |
| 11   | Red     | 1    | True        |
| 12   | Blue    | 2    | False       |
| 13   | Yellow  | 3    | False       |
| 14   | Red1    | 1    | False       |
| 15   | Yellow1 | 3    | True        |
| 16   | Blue1   | 2    | False       |

Note in this above table I have color types in type field and assign status in HasAssigned field also with different primary keys with different names (can be same name - but type is the identifier to find the actual color).

My goal is to get the data as the condition:-

Find the records with the color type with Hasassigned = true or if no records for the type has true. Then type with Hasassigned = false of color type. So my resultset would be;

| 11   | Red     | 1    | True        |
| 12   | Blue    | 2    | False       |
| 15   | Yellow1 | 3    | True        |
| 16   | Blue1   | 2    | False       |

What are all the ways we can do this, with a focus on optimization?

1

If I understand your question correctly, you want all rows with HasAssigned='True'

AND

all rows where HasAssigned='False' where there does not exist a row for that same type that is also HasAssigned='True'

If that is what you want, I think this query will give you that answer:

DECLARE @ColorAssignTable TABLE (
    PKID INT
    ,NAME VARCHAR(30)
    ,Type INT
    ,HasAssigned VARCHAR(10)
    )
insert into @ColorAssignTable(PKID,Name,Type,HasAssigned) values
(11,'Red',1,'True'),
(12,'Blue',2,'False'),
(13,'Yellow',3,'False'),
(14,'Red1',1,'False'),
(15,'Yellow1',3,'True'),
(16,'Blue1',2,'False')

SELECT *
FROM @ColorAssignTable
WHERE HasAssigned = 'True'

UNION ALL

SELECT *
FROM @ColorAssignTable a
WHERE HasAssigned = 'False'
    AND NOT EXISTS (
        SELECT *
        FROM @ColorAssignTable
        WHERE type = a.type
            AND HasAssigned = 'True'
        )
Order by PKID
0

I just tried this way and i could do this;

select * from ColorAssign ci where (ci.HasAssigned = 'True' OR NOT EXISTS(select 1 from ColorAssign sc where sc.Type = ci.Type AND sc.HasAssigned = 'True' ) )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.