Find string in more than one SQL table

Asked

Viewed 593 times

5

I would like to search for a certain string using LIKE in more than 1 table. Here is an example of how I did, which will probably spend a lot of time or will not search in all tables:

string SQL1 = @"SELECT *
    FROM TB_1
    WHERE NOME LIKE '%' + @PRODUTO + '%'";

DataAccess db1 = new DataAccess(SQL1);

db1.addParametro("@PRODUTO", SqlDbType.VarChar, produto);

if (db1.GetDataTable().Rows.Count > 0)
{
    return db1.GetDataTable();
}
else
{
    string SQL2 = @"SELECT *
    FROM TB_2
    ....
}

There’s a better or easier way?

3 answers

7

Using the UNION clause is the best way, see an example:

Let’s say there are two conceptual tables: TB_CLIENTES and TB_VENDAS.

TB_CLIENTES(
    ID,
    NOME,
    ENDERECO_RESIDENCIA,
    CIDADE_RESIDENCIA
)

TB_VENDAS(
    ID,
    CLIENTE_ID,
    VALOR,
    PRODUTO,
    ENDERECO_ENTREGA,
    CIDADE_ENTREGA
)

With the following records:

TB_CLIENTES
  ID    NOME      ENDERECO_RESIDENCIA      CIDADE_RESIDENCIA
  1     "José"    "R. 13 de Maio"          "Monte A. do Sul"
  2     "Maria"   "R. 23 de Maio"          "Serra Negra"
  3     "João"    "R. 7 de Setembro"       "Amparo"

TB_VENDAS
  ID    CLIENTE_ID    VALOR    PRODUTO    ENDERECO_ENTREGA    CIDADE_ENTREGA
  1     3             36.0     "Xícara"     "R. 9 de Abril"       "Pedreira"
  2     2             45.0     "Baú"        "R. 23 de Mario"      "Serra Negra"
  3     1             12.0     "Licor"      "R. João XXIII"       "Amparo"
  4     3             55.0     "Panela"     "R. Maria III"        "Serra"       

There is a need to select all customers and/or purchases that the city contains "Serra" on its behalf.

SELECT "CLIENTE" as TIPO, ID, CIDADE_RESIDENCIA as CIDADE, NOME as DISC
FROM TB_CLIENTES
WHERE CIDADE LIKE '%' + @NOME_CIDADE+ '%'

UNION

SELECT "VENDA" as TIPO, ID, CIDADE_ENTREGA as CIDADE, PRODUTO as DISC
FROM TB_VENDAS
WHERE CIDADE LIKE '%' + @NOME_CIDADE+ '%'

As a result we would have:

TIPO      ID    CIDADE         DISC
"CLIENTE" 2     "Serra Negra"  "Maria"
"VENDA"   2     "Serra Negra"  "Baú"
"VENDA"   4     "Serra"        "Panela"

Note that it is necessary to assemble selects so that data types and column names become common among them, only then will you succeed. And if the types are not the same or the numerical accuracies are different you can use the commands for cast, which each SGDB provides.

2

Try to use the UNION clause

Check the syntax of your database, using UNION

SELECT *
    FROM TB_1
    WHERE NOME LIKE '%' + @PRODUTO + '%'

UNION 
SELECT *
    FROM TB_2
    WHERE NOME LIKE '%' + @PRODUTO + '%'"
  • Column names must be the same if you have an A table a column named string1 and another B string2 use an alias in table B string2 as string1

  • good, what if one table has more columns than another? the problem is this now, gives an error saying that should have the same number columns

  • 1

    Mount SELECT so that you have the same number of columns. Let’s assume that the Column email exists only in the Second table, and not in the first, then In the first table is SELECT Name, Address, NULL AS email

0

It’s probably best to use something like Full Text Search (in the case of SQL Server) or a search tool like Lucene.

Full Text Search is a SQL Server engine. Specific to text search, you choose the tables and columns and it creates an inverted index. It is possible to do several types of search. As words close to each other, synonyms etc

More hints http://technet.microsoft.com/en-us/library/ms142571.aspx

Lucene is already an external library. It will save the data in physical files. It’s market reference and if I’m not mistaken Stackoverflow now uses it.

http://blog.stackoverflow.com/2011/01/stack-overflow-search-now-81-less-crappy/

  • It would be interesting to elaborate a little more your answer. How to use Full Text Search, Lucence, etc.

  • I added more hints. But how to use yourself is already outside the scope of the site

Browser other questions tagged

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