0

We have more than 40 tables under a database called "Coredata" and tables named from 't001', 't002' to 't040'. All table has one common field - 'email' so building a search option using 'email' as a key index, so following is my SQL query to search between few tables - please suggest an easy approach to run the query and search/fetch data across all 40 tables.

Following query pops-up #1052 - Column 'email' in where clause is ambiguous

 SELECT * from t001, t002 WHERE email ="emailid@gmail.com";
Bala Krishnan D
  • 131
  • 1
  • 2
  • 11
  • I would advise you to create a view once with hardcoded UNION all from all tables. Then you can use it like you use on big table. – Lamar Oct 23 '17 at 08:51
  • 1
    I think your database schema should have a master table for all emails where the email is a primary key. Without this, I'd say your schema has a smell. – Tim Biegeleisen Oct 23 '17 at 08:52
  • @Lamar That might work, but it would probably not be too performant since indices could not be used on the subquery. – Tim Biegeleisen Oct 23 '17 at 08:53
  • You missed a `JOIN` between your 2 tables. That's why it is said **where clause is ambigous**. Email from table t001 or t002 ? or Both ? it's your error to not specify the Table. Especially if the email column exists in both tables. – F0XS Oct 23 '17 at 08:53
  • A possible duplicate of https://stackoverflow.com/questions/7523039/mysql-query-to-search-all-tables-within-a-database-for-a-string – Aparajit P Utpat Oct 23 '17 at 08:56

0 Answers0