Compare a table field with another table

Asked

Viewed 2,082 times

2

I am trying to create a query in SQL Server to know if the city that was filled in 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?

Tabela de Municípios

Tabela de Pessoas

sql sql-server

4 answers

1


I didn’t find the question very clear, but I understood what you want to do, as some information is missing 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 subconsultation is displayed with the keyword EXISTS, the subconsulta acts as a test of existence. The WHERE clause of external query tests if the lines returned by the subconsulta exist. The subconsultation does not actually produce any data; it returns a value TRUE or FALSE.

Check : https://technet.microsoft.com/pt-br/library/ms189259(v=sql.105). aspx

  • Ball show Lucas, mt thanks

1

It would be a good thing 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 understand what you want, you will enter a value in a field and you want to know if what was typed is in the table of municipalities... (It would be a good you explain if this search will be done with similar values or exactly equal values too). I’ll put two examples and see if any of the right ones...

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 type '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 brings only what is equal. For example, if the user type 'São' comes nothing of result, but if type 'São paulo', will come the city of São paulo only.

Just remembering that the text 'typeName' is only a reference and it is up to you to exchange this reference for what the user type

  • Good approach too :) +1

  • I edited the question, I think it became clearer what I need, thanks for the help Matheus

0

I would do it that way, if Count is higher than zero the municipality exists in your DB. I’m guessing your table has the Field Municipio and Id, ai Voce adapts to your need!

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

declare @Count int

set @count = (select count(Id) from GMUNICIPIO where Municipio = @municipio)

  • Thiago, I edited the question, I think it’s now become a little clearer what I meant, can you help me?

  • I understood the question, the approach I posted is directly in SQL, in my example @municipio will be the user input, so in SQL I’m comparing what the user typed with what you have in DB. Put here also the structure of your table please.

  • Thiago, I printed the tables, if you notice, in the county table the cities are registered only with the first capital letter, but when the person goes to register, the field is free, therefore, it fills in the way he wants, then I want to compare the city field of the person table with the field "county name", if you have different, I will correct.

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 table municipio through the column [EXIST] and their respective name through the column [NAME]

Browser other questions tagged sql sql-server

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