Home / Questions / 512708
Accepted
Popularfan
Asked: 2022-07-04 03:00:23 +0800 CST 2022-07-04 03:00:23 +0800 CST

SQL show only the records of the groups that meet a condition

  • 772

Having these records in a table:

ID Marca Version TipoVersion CodEstadoMarca

1   XX   0      PPP        C
2   XX   1      CKL        C
3   XX   2      CV         C
4   XX   3      DDD        C 
5   XX   4      ANU        D
6   JJ   0      PPP        C
7   JJ   1      CV         C
8   JJ   2      CV         C
9   JJ   3      DDD        C
10  NN   0      PPP        C
11  NN   1      KKK        C
12  AA   0      PPP        C
13  AA   1      CV         C

I need it to show me the records of the Trademarks where it is true that there is no codEstadoMarca 'D' or a TipoVersion 'ANU' and there is also a 'CV' in tipoVersion

That is, it should show the following:

ID  Marca Version TipoVersion CodEstadoMarca

6     JJ   0      PPP        C
7     JJ   1      CV         C
8     JJ   2      CV         C
9     JJ   3      DDD        C
12    AA   0      PPP        C
13    AA   1      CV         C

I am using SQL SERVER from Microsoft.

One solution I'm trying is:

SELECT DISTINCT T0.Marca FROM TABLA T0 WHERE CodEstadoMarca = 'D'

With this it shows me the Brand code that contains some 'D'

Now the next thing would be to make a select of all the Marks and only show the ones that do not appear in the result of the previous Select. But I don't know how to link it

sql

1 Answers

  1. Best Answer

    I understand that you are looking for a non-inclusive CodEstadoMarca = 'D' OR TipoVersion = 'ANU'and an inclusive condition tipoVersion = 'CV', you could do something like this

    WHERE Marca NOT IN (SELECT DISTINCT T0.Marca 
                           FROM TABLA T0 
                           WHERE CodEstadoMarca = 'D' OR TipoVersion = 'ANU')
      AND Marca IN (SELECT DISTINCT T0.Marca 
                           FROM TABLA T0 
                           WHERE tipoVersion = 'CV')
    • 2