Select Same Value From Multiple Tables

Question

I have 3 tables: user, physician, care_provider that all have a username and email field.

I need to ensure that the username and email created for any of the 3 user types is unique across all 3 tables.

Is there a way to join or union the tables to check for the same data in each?

Obviously I could do 3 selects, but of course I'd prefer to check the username with a single query.

Right now I'm just checking all 3 like so:

$count = 0;
$user = $this->db->query("
    SELECT COUNT(user_id) AS total 
    FROM " . DB_PREFIX . "user 
    WHERE username = '" . $this->db->escape($username) . "'
");
if ($user->row['total']):
    $count += $user->row['total'];
endif;
... (for each table) ...

return $count;

Read this http://stackoverflow.com/questions/409705/mysql-selecting-data-from-multiple-tables-all-with-same-structure-but-differen — user1954544, Apr 07 '14 at 20:08
Can i assume that 1) any username is **only** in one table?. 2) The email address is uniquely associated with one user. i.e, any one email will only be in one of the tables and related to one user? — Ryan Vincent, Apr 08 '14 at 00:06
Yes that's correct. No email or username can be used in more than one table. It would have been easier if we could have built all these users into the same table but there were just too many differences in user types so we had to break them up. — secondman, Apr 08 '14 at 14:39

score 0 · Answer 1 · edited May 23 '17 at 11:49

0

SELECT COUNT(*) AS total FROM table1, table2, table3 
WHERE table1.username = input
OR table2.username = input
OR table3.username = input

Or you can do something like this question. mySQL select count, multiple tables

edited May 23 '17 at 11:49

Community

1
1

answered Apr 07 '14 at 20:05

Joshua Bixler

531
2
6

score 0 · Answer 2 · answered Apr 08 '14 at 14:42

Here's what ended up working:

$query = $this->db->query("
    SELECT SUM(total) AS total 
    FROM(
        SELECT COUNT(*) AS total 
            FROM " . DB_PREFIX . "user 
            WHERE username = '" . $this->db->escape($username) . "' 
            UNION ALL
        SELECT COUNT(*) AS total 
            FROM " . DB_PREFIX . "care_provider 
            WHERE username = '" . $this->db->escape($username) . "' 
            UNION ALL
        SELECT COUNT(*) AS total 
            FROM " . DB_PREFIX . "physician 
            WHERE username = '" . $this->db->escape($username) . "'
    ) AS t 
");

return $query->row['total'];

Select Same Value From Multiple Tables

2 Answers2