How to get the data from same table by different conditions in SQL?

Question

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?

Answer 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

Answer 2

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' ) )