Find string in more than one SQL table

Question

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?

Answer 1

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.

Answer 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 + '%'"

Answer 3

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/