CODEWITHSUNDEEP
mysql
avishek gurung
avishek gurung

Reputation: 188

Mysql Query to select all the tables with same column name

To be specific I am using MySql.I have many tables in my database, like employee, student, employer etc etc. There are more than 20 such tables. And in each table, I have a column called 'username' and 'age'. Now I want a query that will give me the all the ages when i give a particular username='alex'.

Upvotes: 0

Views: 1623

Answers (3)

Naveen Kumar
Naveen Kumar

Reputation: 4601

Query to get and tablenames having a particular column

select table_name from information_schema.columns 
where table_name in (
select table_name from information_schema.tables
where table_schema='databaseName'
) and 
column_name='username';

from the above query you will be all tablenames which have the field username then i PHP you can build query to get values from all the tables;

Upvotes: 0

Jasper
Jasper

Reputation: 11908

It looks like poor design of the database and if you can change the database structure, that's probably the way to go. Otherwise, you could try this:

(SELECT username, age
 FROM table1)
UNION
(SELECT username, age
 FROM table2)
UNION
(SELECT username, age
 FROM table3)
...

Upvotes: 0

eggyal
eggyal

Reputation: 126005

You probably should have a single table (say, Users) which has a column indicating what type of person that user is (employee, student, employer, etc.). However, if you must query across multiple tables of this sort, use UNION:

SELECT age FROM employee WHERE username = 'alex' UNION ALL
SELECT age FROM student  WHERE username = 'alex' UNION ALL
SELECT age FROM employer WHERE username = 'alex' -- etc.

Upvotes: 1

Related Questions