MySql find match in any of three tables?

Question

I'm trying to find a match for an IP address in any one of three tables but my query tells me the column I'm looking in is ambiguous?

SELECT COUNT(*) 
FROM `zz_tview`,`zz_tview1`,`zz_tview3` 
WHERE `ipaddress` ="192.168.01.01"

So I get this message "Column 'ipaddress' in where clause is ambiguous", how would I construct this firstly so it works and secondly so the query isn't too heavy as the tables have many thousands of rows?

Answer 1

Sounds like you want this, but this is going to produce a cartesian result which is most likely not the result that you want:

SELECT COUNT(*) 
FROM `zz_tview`,`zz_tview1`,`zz_tview3` 
WHERE `zz_tview`.`ipaddress` ="192.168.01.01"
  OR `zz_tview1`.`ipaddress` ="192.168.01.01"
  OR `zz_tview3`.`ipaddress` ="192.168.01.01"

You should really construct this as a JOIN:

SELECT COUNT(*) 
FROM `zz_tview` v
INNER JOIN `zz_tview1` v1
    ON v.id = v1.id  --- use the column that would join these values
INNER JOIN `zz_tview3` v3
    ON v.id = v3.id  --- use the column that would join these values
WHERE v.`ipaddress` ="192.168.01.01"
      OR v1.`ipaddress` ="192.168.01.01"
      OR v3.`ipaddress` ="192.168.01.01"

If you have no way to JOIN the tables, then you can use something similar to this:

select sum(total)
from
(
    SELECT count(*) as `total`
    FROM `zz_tview` v
    where v.`ipaddress` ="192.168.01.01"
    union all
    SELECT count(*) as `total`
    FROM `zz_tview1` v1
    where v1.`ipaddress` ="192.168.01.01"
    union all
    SELECT count(*) as `total`
    FROM `zz_tview3` v3
    where v3.`ipaddress` ="192.168.01.01"
) src