Compare one field of a table with another table

2

I am trying to create a query in SQL Server to know if the city that was filled at the time of registration that the person did, is equal to a city field of another table that already has all the municipalities of Brazil, how can I do This?

    
asked by anonymous 27.12.2017 / 11:43

4 answers

1

I did not find the question very clear, but I understood what you want to do, as I lack some information I believe you want to compare the information in the table. For this there is a command called EXISTS

SELECT CIDADE FROM PESSOA P
WHERE EXISTS
    (SELECT * FROM GMUNICIPIO WHERE P.CIDADE = GMUNICIPIO.NOMEMUNICIPIO)
  

When a subquery is presented with the keyword EXISTS, the   subquery functions as a test of existence. The WHERE clause of the   external query tests if the rows returned by the subquery   exist. The subquery does not actually produce any data; it returns a   value TRUE or FALSE.

Check: link

    
27.12.2017 / 16:13
1

It would be good if you put the table next to your question (An image with the table structure, for example).

But I read your question and if I understood what you want, you will enter a value in a field and you want to know if what was entered is in the municipalities table ... (It would be a good idea to explain if this search goes be made with similar values or exactly the same values as well). I'll put two examples and see if any of the right ...

SELECT nome_cidade FROM GMUNICIPIOS WHERE nome_cidade LIKE '%nome_digitado%';

This first example will bring all the results of texts similar to what was typed. for example, if the user types 'São' will bring São Paulo, São Vicente, São Caetano ..

SELECT nome_cidade FROM GMUNICIPIOS WHERE nome_cidade = 'nome_digitado'

This second example after just what is equal. For example, if the user types 'Sao' there is no result, but if you type 'Sao paulo', the city of Sao paulo will only come.

Just remember that the text 'name_digit' is just a reference and it is up to you to change this reference by what the user type

    
27.12.2017 / 12:01
0

I would do it this way, if the count is greater than zero the municipality exists in your DB. I'm guessing that your table has the field Municipio and Id , so you can adapt to your need!

declare @municipio varchar(50)
set @municipio = 'Santos'

declare @Count int

set @count = (select count(Id) from GMUNICIPIO where Municipio = @municipio)
    
27.12.2017 / 12:02
0

Hello, Marcilio, my suggestion would be to use a simple join:

SELECT CAST(CASE WHEN GMUNICIPIO.CODMUNICIPIO IS NULL THEN 0 ELSE 1 END AS BIT) AS EXISTE, PPESSOA.NOME FROM PPESSOA LEFT JOIN GMUNICIPIO ON PPESSOA.CIDADE =  GMUNICIPIO.NOMEMUNICIPIO

With the above query you will see the people who have the registration coinciding with the municipality table through the [EXIST] column and its name through the [NAME] column

    
27.12.2017 / 13:21