Searching all tables

Question

I'm having a little problem. I have 2 tables in my database:

servico, with id, nome, profissao (plumber, electrician,e tc), data and local.

canalizador, with id, nome, profissao (only plumber), data and local.

I have this code so that I can get to the "canalizador" table:

mysql_connect($host,$user,$passwd) or die (mysql_error());
mysql_select_db($database);

$query = "SELECT * FROM canalizador WHERE profissao LIKE '$profissao'";
$result = mysql_query($query) or die(mysql_error());
$casaArray = array();

But I need a code where I can search all the tables like the "canalizador" table by just pressing the button of each career(profissao) in the menu. If I do this:

mysql_connect($host,$user,$passwd) or die (mysql_error());
mysql_select_db($database);

$query = "SELECT * FROM canalizador, carpinteiro WHERE profissao LIKE '$profissao'";
$result = mysql_query($query) or die(mysql_error());
$casaArray = array();

I have this error:

Column 'profissao' in where clause is ambiguous

That error cause is that "profissao" does exist inside both table, you can use canalizador.profissao to tell sql which table (you can use an AND condition to tell it to both table for example) — Goikiu, Jun 11 '15 at 07:19
But why do you have two tables with the same columns and very similar contents? — jarlh, Jun 11 '15 at 07:51
1) You have a table design problem, because you haven't followed good normalization practices. In most well-designed systems, there will only be one table linking names to professions. 2) You are wide open to [SQL Injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Fix your query or be subject to data theft or worse. — Clockwork-Muse, Jun 11 '15 at 08:54

jarlh · Answer 1 · 2015-06-11T07:48:13.623

2

Do a UNION ALL with the two tables, use LIKE in the result (as a derived table):

select id, nome, profissao, data, local 
from
(
select id, nome, profissao, data, local from servico
UNION ALL
select id, nome, profissao, data, local from canalizador
) dt
where dt.profissao LIKE '$profissao'

Alternatively, as suggested by AsConfused, just a UNION ALL:

select id, nome, profissao, data, local from servico
where profissao LIKE '$profissao'
UNION ALL
select id, nome, profissao, data, local from canalizador
where profissao LIKE '$profissao'

edited Jun 11 '15 at 07:48

answered Jun 11 '15 at 07:29

jarlh

42,561
8
45
63

You involved 3 selects when 2 would suffice. Explain would show degradtion of performance. Which could be significant considering the two inside filter nothing and bring it all then at end it says i only want this filter – AsConfused Jun 11 '15 at 07:41
Perhaps you're right. I don't know how MySQL optimizes queries like this. I'll add your suggested version too. – jarlh Jun 11 '15 at 07:46
The 2 inside are tablescans. The outer dt is derived and therefore cannot use an index – AsConfused Jun 11 '15 at 07:49

score 0 · Answer 2 · answered Jun 11 '15 at 07:24

Try this:

mysql_connect($host,$user,$passwd) or die (mysql_error());
mysql_select_db($database);

$query = "SELECT * FROM canalizador c1, carpinteiro c2 WHERE c1.profissao LIKE '$profissao'";
                                                          // ^ c1 or c2 
$result = mysql_query($query) or die(mysql_error());
$casaArray = array();

Stanislovas Kalašnikovas · Answer 3 · 2015-06-11T07:28:36.347

0

First of all is bad practice to use comma separated tables like this:

SELECT * FROM canalizador, carpinteiro, etc...

You need to use JOINS and list column names in SELECT clause like this:

SELECT col1, col2, col3 
FROM canalizador can 
JOIN carpinteiro car ON can.Id = car.Id

Then specify from which table you want to use profissao column

WHERE can.profissao LIKE '$profissao

edited Jun 11 '15 at 07:28

answered Jun 11 '15 at 07:24

Stanislovas Kalašnikovas

9,203
4
32
59

U sure u want your first line to say that? Or your last – AsConfused Jun 11 '15 at 07:26

Bud Damyanov · Answer 4 · 2015-06-11T07:27:01.053

0

As first option you can give your column name an alias with AS keyword, i.e:

SELECT canalizador.column AS col1, carpinteiro.column AS col2 
FROM canalizador, carpinteiro WHERE profissao LIKE '$profissao'

As second option you can rework your SQL query and use MySQL JOIN to combine multiple table results, read more about how to join tables here and more general information here.

edited Jun 11 '15 at 07:27

answered Jun 11 '15 at 07:24

Bud Damyanov

30,171
6
44
52

Better to qualify as table.column! – jarlh Jun 11 '15 at 07:26

Searching all tables

4 Answers4