NOT EXIST? How to use [closed]

Question

NOT EXIST? How to use [closed]

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (1 votes)

0

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

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

Can anyone help me?

sql

asked by anonymous 01.06.2017 / 20:13

3 answers

How to convert text field with mask to double problems submitting form

score 1 · Answer 1

Thick mode 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)

score 1 · Answer 2

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 query :

The IN will filter the companies that have plan A, B, C, D OR E;
The NOT EXISTS will check if there is any record in the FRANQUIAS table using the dummy field ID_EMPRESA to link the tables. If there is no record, the line will be returned.

Subqueries with EXISTS or NOT EXISTS

If 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

score 1 · Answer 3

NOT EXISTS works against EXISTS. The WHERE clause in NOT EXISTS will be met if no row is returned by the subquery.

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