Clever Geek Handbook

How do I get a common column from multiple tables in a database?

Greeting to all smart people around here !!

I came across a strange SQL interview question.

Qn. If I have 100 tables in Database. I want to fetch common records from Each table.

For example, location

is a common field in 100 tables. I want to get a field location

from all tables without mentioning the name of each table in my SQL query.

Is there a way to do this?

If there is any possibility, let me know ...

+3


source to share


5 answers


A friend of mine found the answer to my question.

To get a shared column from multiple tables, use INFORMATION_SCHEMA.COLUMNS and the shared column name.



Request: select *from information_schema.columns where column_name='column name'

Hope it helps!

0


source


get a list of tables from db metadata and then query each one:



Statement stmt = conn.createStatement();
ResultSet locationRs = null;
DatabaseMetaData md = conn.getMetaData();
ResultSet rs = md.getTables(null, null, "%", null);
while (rs.next()) {
    locationRs = stmt.executeQuery("SELECT location from "+ rs.getString(3));
    System.out.println(locationRs.getString(1));
}
+2


source


In MSSQL server, you have INFORMATION_SCHEMA.COLUMNS table which contains column names, so you can use group and count some value, after that you will get column name, after which you can use pivot point to get column name values ​​and continue to it ... You will receive ans.

For example,

Select COLUMN_NAME from group INFORMATION_SCHEMA.COLUMNS BY COLUMN_NAME with score (COLUMN_NAME)> 2

For the above query you will get the common column names

0


source


You can try this for any number of tables in the DB:

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS  group by COLUMN_NAME having count(COLUMN_NAME)=(select count(*) from INFORMATION_SCHEMA.TABLES)
0


source


I am assuming you already have a connection object and statemnt. Now try this: it might work for you if you don't make some adjustments with loops and conditions. Also, you need to have two ResultSet ex objects: rs1 and rs2.

DatabaseMetaData dbmd = con.getMetaData ();
  Line table [] = {"TABLE"} `;
      rs1 = dbmd.getTable (null, null, ". *", table); 
while(rs1.next()){  
    String tableFrom = rs1.getString(3) ;
    rs2 =  dbmd.getColumns(null,null,tableFrom , ".*") ;  
     while(rs2.next()) { 

        String locColFrom = rs2.getString(3);  

        if(locColFrom .equalsIgnoreCase("location")) 
             stmt.executeQuery(select locColFrom from  tableFrom ) ;
        }
}

Here's a link to explore [Oracle] ( http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getTables(java.lang.String,%20java.lang.String,% 20java.lang.String,% 20java.lang.String []) )

0


source






More articles:


All Articles