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 ...
source to share
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));
}
source to share
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
source to share
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 []) )
source to share