Geek asks and answers

SQL getting a different identifier

I have a table with the following data:

ID   NAME    ATTRIBUTE   CODE
=============================
1     XX         MM        GC
1     XX         ST        GC
2     ZZ         LL        GC
2     ZZ         ST        GC
3     AA         MM        PC

I need a IDname from a table that contains either MMAttribute code , or GC, but not both. As in the request, you need to get the numbers IDonly 2 and 3, but not 1.

How to do it?

+3
source share
6 answers
Select Id, Name

From #t
Where (Attribute = 'MM' Or Code = 'GC')
And Id Not In (Select Id From #t 
                Where (Attribute = 'MM' And Code = 'GC'))
+2
source 
select distinct T1.ID,
                T1.NAME
from T as T1
where (T1.ATTRIBUTE = 'MM' or T1.CODE = 'GC') and
      not exists (select *
                  from T as T2
                  where T1.ID = T2.ID and
                        T2.Attribute = 'MM' and
                        T2.CODE = 'GC')

Result:

ID  NAME
2   ZZ
3   AA
+1
source

Try the following:

select distinct ID
from TableName
where (Attribute = 'MM' or Code = 'GC')
  and not (Attribute = 'MM' AND Code = 'GC')
0
source

Try the following:

SELECT ID, NAME
  FROM <YOUR-TABLE-NAME>
 WHERE 
 (ATTRIBUTE = 'MM' AND CODE <> 'GC') OR
 (ATTRIBUTE <> 'MM' AND CODE = 'GC') ;
0
source

choose a separate identifier, name from tbl? where (attribute = 'MM' or code = 'GC') and not (attribute = 'MM' and code = 'GC')

0
source 
(
    SELECT ID, NAME FROM TableName WHERE CODE = 'GC'
    EXCEPT
    SELECT ID, NAME FROM TableName WHERE ATTRIBUTE = 'MM'
)
UNION
(
    SELECT ID, NAME FROM TableName WHERE ATTRIBUTE = 'MM'
    EXCEPT
    SELECT ID, NAME FROM TableName WHERE CODE = 'GC'
)
0
source

More articles:


All Articles