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?
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
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
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)
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