NOT EXIST ? How to use

Asked

Viewed 899 times

0

Person, I need a help, need to filter only people who have X, Y and Z plans that are not on the other table

For example: I have a table of plans, where within it has 50 plans, however, only 5 have some product characteristics, and in another table of Franchises there are all companies that have in 5 plans. (but there is a flaw that not all companies that should have been registered in the Franchise table are there) I need to filter the companies that have Plan A,B,C,D,E that are in the COMPANIES table, that are not registered in the Franchises table

Someone can help me?

sql

  • You could inform the query of how far you were able to advance.

  • I recommend reading @Wesley: https://answall.com/questions/62925/not-in-ou-not-exists-qual-use

  • @Wesleygiovedi: what is the database manager: Oracle Database? Mariadb? SQL Server? other?

  • Has any response helped solve the problem and can address similar questions from other users? If so, make sure to mark the answer as accepted. To do this just click on the left side of it (below the indicator of up and down votes).

3 answers

1

Roughly would be something like

select *
from   empresas
where  plano in ('A','B','C','D','E')
and    not exists (select null
                   from   Franquias
                   where  Franquias.cod_empresa = empresas.cod)

1

In your case the result would be as follows:

SELECT E.*
  FROM EMPRESAS E
 WHERE E.PLANO IN ('A', 'B', 'C', 'D', 'E')
   AND NOT EXISTS(SELECT 1
                    FROM FRANQUIAS F
                   WHERE F.ID_EMPRESA = E.ID_EMPRESA)

Explaining the query:

  • The IN filter undertakings with Plan A, B, C, D or E;
  • The NOT EXISTS check if there is any record in the table FRANQUIAS using the fictional field ID_EMPRESA to link the tables. If there is no record, the line will be returned.

Subqueries with EXISTS or NOT EXISTS

If a subquery Returns any Rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE

Or in free translation:

If the subquery returns any line, EXISTS will BE TRUE, and NOT EXISTS will BE FALSE

1

NOT EXISTS works contrary to EXISTS. The WHERE clause in NOT EXISTS will be met if no line is returned by the sub-label.

  SELECT Name
    FROM Production.Product
    WHERE NOT EXISTS
        (SELECT * 
         FROM Production.ProductSubcategory
         WHERE ProductSubcategoryID = 
                Production.Product.ProductSubcategoryID
            AND Name = 'Wheels')

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.