Find string in more than one SQL table

Question

Find string in more than one SQL table

Navigation

#1 by (7 votes)
#2 by (2 votes)
#3 by (0 votes)

5

I would like to fetch a given string using LIKE in more than 1 table. Here is an example of how I did it, which will probably spend a lot of time or not fetch 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
    ....
}

Is there a better or easier way?

sql c# ado.net

asked by anonymous 06.03.2014 / 19:37

3 answers

How do I make Django "see" CSS? How to search for images in Google with JavaScript?

score 7 · Answer 1

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

Let's say that 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" in their name.

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 mount the 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 precisions are different you can use the cast commands that each SGDB provides.

score 2 · Answer 2

Try using the UNION clause

Check the syntax of your database, when using UNION

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

UNION 
SELECT *
    FROM TB_2
    WHERE NOME LIKE '%' + @PRODUTO + '%'"

score 0 · Answer 3

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

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