Search in 3 unrelated Mysql tables [duplicate]

Question

Search in 3 unrelated Mysql tables [duplicate]

Navigation

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

2

On a system, I have 03 internal search types:

And I would like to look at 03 unrelated tables:

table1, table2, and table3

For this, 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, gave the error below:

1222 - SELECT statements used have different number of columns

I tried this search and also could not:

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

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

mysql

asked by anonymous 28.05.2018 / 03:48

3 answers

1

try 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 this way 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 % must be used with the LIKE operator.

In the reference I'm suggesting, unlike the example I gave, it better explains how UNION , maybe I can help you.

28.05.2018 / 04:00

1

You can check for a record that starts with Davi in one of the three tables using a subquery like this:

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

28.05.2018 / 04:26

I need to list a text field with values separated by; on () and bind () are not working

score 2 · Accepted Answer

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 it working

EDIT

To select ALL, you only have to 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;

The simple UNION runs a DISTINCT in the SELECT. That's why he ignores equal results.

View the result here