Search 3 unrelated Mysql tables

Asked

Viewed 243 times

2

In one system, I have 03 types of internal search:

inserir a descrição da imagem aqui

And I would like to search in 03 unrelated tables:

Tabela1, table2 and Tabela3

For that, I tried the commands below:

SELECT * FROM `tabela1` WHERE NomeUsuarios LIKE 'Davi%' UNION SELECT * FROM `tabela2` WHERE NomeUsuarios LIKE 'Davi%' UNION SELECT * FROM `tabela3` WHERE NomeUsuarios LIKE 'Davi%'

The top one, made the mistake below:

1222 - The SELECT commands used have different number of columns

I tried that search and I couldn’t either:

SELECT * FROM `tabela1`, `tabela2`, `tabela3` WHERE tabela1.NomeUsuarios LIKE 'Davi%' OR tabela2.NomeUsuarios LIKE 'Davi%'  OR tabela3.NomeUsuarios LIKE 'Davi%'

He returns to me all the names, regardless of whether they are David or not. How can I do this search?

mysql

3 answers

2


The problem is that you are not using LIKE to do your search. The right thing is:

(SELECT * FROM tabela1 WHERE tabela1.NomeUsuarios LIKE 'Dav%')
UNION 
(SELECT * FROM tabela2 WHERE tabela2.NomeUsuarios LIKE 'Dav%')
UNION 
(SELECT * FROM tabela3 WHERE tabela3.NomeUsuarios LIKE 'Dav%')
ORDER BY NomeUsuarios;

See working

EDIT

To select ALL, just use UNION ALL. So:

SELECT * FROM tabela1 WHERE tabela1.NomeUsuarios LIKE 'Dav%'
UNION ALL
SELECT * FROM tabela2 WHERE tabela2.NomeUsuarios LIKE 'Dav%'
UNION ALL
SELECT * FROM tabela3 WHERE tabela3.NomeUsuarios LIKE 'Dav%'
ORDER BY NomeUsuarios;

Simple UNION executes a DISTINCT in SELECT. Hence it ignores equal results.

See the result here

  • Guys, I’m actually using Like. In the post I put manually and I ended up putting wrong, but I adjusted my post. I think it’s some problem in the configuration of mysql local or something. I’ll try it remotely to see if it works.

  • About the problem of error #1222, fixed as follows, I changed the query to: SELECT tabela1.NomeUsuarios FROM tabela1 WHERE tabela1.NomeUsuarios LIKE 'Davi%' UNION SELECT tabela2.NomeUsuarios FROM tabela2 WHERE tabela2.NomeUsuarios LIKE 'Davi%' UNION SELECT tabela3.NomeUsuarios FROM tabela3 WHERE tabela3.NomeUsuarios LIKE 'Davi%', but even having the name David in the 03 tables, he only returned me 1. In fact I need to bring the 03 results of the 03 tables.

  • @Fox.11 take a look at my issue.

  • 1

    Thanks Andrei. It worked!

  • 1

    @Fox.11 quiet! Hug!

1

try like this:

SELECT * FROM tabela1 WHERE tabela1.NomeUsuarios = 'Davi%' 
UNION 
SELECT * FROM tabela2 WHERE tabela2.NomeUsuarios = 'Davi%' 
UNION 
SELECT * FROM tabela3 WHERE tabela3.NomeUsuarios = 'Davi%'

I tested it like that and it worked:

SELECT * FROM 
tabela1,
tabela2,
tabela3
WHERE
tabela1.NomeUsuarios LIKE 'Davi%' AND
tabela2.NomeUsuarios LIKE 'Davi%' AND
tabela2.NomeUsuarios LIKE 'Davi%' AND

The % should be used with the operator LIKE.

In the reference I am suggesting, different from the example I gave, explains better the functioning of UNION, maybe I can help you.

  • 1

    Hello Wees. I’ve tried that way, but the error persists.

  • Take a look at the reference that I mentioned agr, maybe it helps to understand UNION, I will look for other ways to show

  • Okay Wees. I’ll take a look. Thank you.

  • I found a solution that worked in my tests, take a look

  • 1

    only changes to OR in place of AND in his second SELECT because this second you did it will only return if all tables have the value LIKE 'Davi%'. I believe that’s not what he wants because UNION works like an OR and he put the OR in his second select of the question.

1

You can check if there is a record that starts with Davi in one of the three tables using a subquery in this way:

SELECT * 
FROM (
    SELECT * FROM tabela1
    UNION
    SELECT * FROM tabela2
    UNION
    SELECT * FROM tabela3
) t
WHERE NomeUsuarios LIKE 'Davi%'

Browser other questions tagged mysql

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