How to get column name and column lable when given query have inner query in left join

left outer join
inner join
sql joins
how to retrieve data from two tables with one sql statement
sql join multiple tables with conditions
left outer join 3 tables example
full outer join
left join vs inner join

Can anybody help me to get actual column name and its alias using ResultSetMetaData

    class MysqlCon {
    public static void main(String args[]) {
        Connection con = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
            Statement stmt = con.createStatement();
            String query = "SELECT \n" + 
                    "  RA_Min_1 AS 'ran Min',\n" + 
                    "  RA_Max_1 AS 'ran Max' \n" + 
                    "FROM\n" + 
                    "  `Masters` m \n" + 
                    "  LEFT JOIN \n" + 
                    "    (SELECT \n" + 
                    "      mr.`Master_ID`,\n" + 
                    "      `RA_Min_1`,\n" + 
                    "      `RA_Max_1` \n" + 
                    "    FROM\n" + 
                    "      `Master_Attribute_Range` mr \n" + 
                    "      INNER JOIN `Masters` m \n" + 
                    "        ON m.`Master_Type_ID` = 3 \n" + 
                    "        AND mr.`Master_ID` = m.`Master_ID`)  mar\n" + 
                    "    ON mar.Master_ID = m.`Master_ID` \n" + 
                    "    AND m.`Master_Type_ID` = 3 \n" + 
                    "ORDER BY RA_min_1 \n" + 
                    "LIMIT 0, 25 ";

            ResultSet rs = stmt.executeQuery(query);

            ResultSetMetaData rsm =rs.getMetaData();
            for (int i = 1; i <= rsm.getColumnCount(); i++) {
                System.out.println(rsm.getColumnLabel(i) + "--" + rsm.getColumnName(i));
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

output

ran Min--ran Min
ran Max--ran Max

Here, if i remove ORDER BY RA_min_1 clause then it will return expected output:

ran Min--RA_Min_1
ran Max--RA_Max_1

UPDATE

First of all Sorry, as I am unable to reply as I had some personal issues, and due to those issues I am not able look stack over flow. please forgive me for that. Thanks.

Normal query without sub query will give result as expected. but I have a situation where i have to use sub queries and need both alias and actual field name.

To put question here, I have truncated sub query with only one field, but in real scenario, sub query after LEFT JOIN is very big and contains many columns like RA_Min_1, RA_Max_1,RA_Min_2, RA_Max_2,...RA_Min_50, RA_Max_50 and some other columns.

We are preparing dynamic query in which some time we have RA_Min_1, RA_Max_1 and sometime RA_Min_2, RA_Max_2.

So, here I am looking for solution with sub query.

Any help will be Appreciated!!

Thanks.

This query should give you the same result as yours:

SELECT
    RA_Min_1 AS 'ran Min',
    RA_Max_1 AS 'ran Max'
FROM `Masters` m
LEFT JOIN `Master_Attribute_Range` mr ON m.`Master_Type_ID` = 3 AND mr.`Master_ID` = m.`Master_ID`
ORDER BY RA_min_1
LIMIT 0, 25;

Does it fix your label prblem?

Find all tables containing column with specified name, How do I find a particular column name in SQL Server? In this query i select column_name,column_type and table_name for more details . I use order by column_type so i can see it easily. SELECT `COLUMN_NAME`,COLUMN_TYPE,TABLE_NAME FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='yourdatabasename' order by DATA_TYPE; If you want to check only double type filed then you can do it easily

Let's look at the full query as multiline text.

SELECT 
  RA_Min_1 AS 'ran Min',
  RA_Max_1 AS 'ran Max' 
FROM
  `Masters` m 
  LEFT JOIN 
    (SELECT 
      mr.`Master_ID`,
      `RA_Min_1`,
      `RA_Max_1` 
    FROM
      `Master_Attribute_Range` mr 
      INNER JOIN `Masters` m 
        ON m.`Master_Type_ID` = 3 
        AND mr.`Master_ID` = m.`Master_ID`)  mar
    ON mar.Master_ID = m.`Master_ID` 
    AND m.`Master_Type_ID` = 3 
ORDER BY RA_min_1 
LIMIT 0, 25

The selected list of fields are ambiguous. The subquery aliased as mar has RA_Min_1 and RA_Max_1 fields so that when this is joined with masters table, the available fields will be m.RA_Min_1, m.RA_Max_1, mar.RA_Min_1, mar.RA_Max_1.

If fields from the subquery are selected either explicitly or implicitly. For example

SELECT 
  mar.RA_Min_1 AS 'ran Min',
  mar.RA_Max_1 AS 'ran Max' 
-- ...
ORDER BY mar.RA_min_1
LIMIT 0, 25

Output

ran Min--ran Min
ran Max--ran Max

Then the result set loses information about column name in the result set metadata.

In order to keep the original column name, selected fields should be from the table when it is possible.

SELECT 
  m.RA_Min_1 AS 'ran Min',
  m.RA_Max_1 AS 'ran Max' 
-- ...
ORDER BY m.RA_min_1
LIMIT 0, 25;

Output

ran Min--RA_Min_1
ran Max--RA_Max_1

The query can be refactored as the sub-select query is joining on the same table in the select query.

SELECT m.RA_Min_1 AS 'ran Min', m.RA_Max_1 AS 'ran Max' FROM Masters m LEFT JOIN (SELECT mr.Master_ID, RA_Min_1, RA_Max_1 FROM Master_Attribute_Range mr INNER JOIN Masters m ON m.Master_Type_ID = 3 AND mr.Master_ID = m.Master_ID ) mar ON mar.Master_ID = m.Master_ID AND m.Master_Type_ID = 3 ORDER BY m.RA_min_1 LIMIT 0, 25

which is

SELECT 
  m.RA_Min_1 AS 'ran Min',
  m.RA_Max_1 AS 'ran Max' 
FROM
  Masters AS m
  LEFT JOIN   
  Master_Attribute_Range AS mr 
  ON m.Master_Type_ID = 3 
  AND mr.Master_ID = m.Master_ID
ORDER BY m.RA_min_1
LIMIT 0, 25

How to select a column name with spaces in MySQL?, How do I join a column from another table in SQL? Column-name join. The column-name join is like a natural join, but it’s more flexible. In a natural join, all the source table columns that have the same name are compared with each other for equality. With the column-name join, you select which same-name columns to compare. You can choose them all if you want, making the column-name join

Darshan, try creating an index on the RA_min_1 column and see if that helps.

SQL joins and how to use them, How can check column name in all tables in SQL Server? I have used Query something like this to get all the Table columns which are having nearly same name like below [Quote] select u.owner AS OWNERNAME, u.table_name as Source_TableName,u.column_name

Here is another approach that could work. Use only the column name in you existing query's top level select and pipe it to another outer select where you select using the label.

An example to demonstrate this:

mysql> select col_x as label_col_x from table order by col_x;

becomes

mysql> select col_x as label_col_x from (select col_x from table order by col_x) as label_table;

In the above, label_table is label for the inner select output and is required by MySQL.

JBoss 3.2 Deployment and Administration, How do I add a column name to a space in MySQL? Get Column Names From Table Example 2. This Frequently asked Questions explains how to find the list of Column names in a Table using sys.columns.-- Query to Get Column Names From Table in SQL Server USE [SQL Tutorial] GO SELECT name FROM sys.columns WHERE OBJECT_ID = OBJECT_ID('NewCustomers') OUTPUT

the inner join clause is useless, you could only Left join and it give you exactly the output needed :

SELECT
    RA_Min_1 AS 'ran Min',
    RA_Max_1 AS 'ran Max'
FROM Masters m
LEFT JOIN Master_Attribute_Range mr ON m.Master_Type_ID = 3 AND mr.Master_ID = m.Master_ID
ORDER BY RA_min_1
LIMIT 0, 25;

INNER JOIN gets all records that are common between both tables based on the supplied ON clause.

LEFT JOIN gets all records from the LEFT linked table and the common columns from the RIGHT table.

Joins and aliases, . The symbol is ( ` `). Back tick is displayed in the keyboard below the tilde operator ( ~). Table.Column. 4/20/2020; 2 minutes to read; In this article Syntax Table.Column(table as table, column as text) as list About. Returns the column of data specified by column from the table table as a list. Example 1. Returns the values from the [Name] column in the table.

Oracle LEFT JOIN By Practical Examples, Different joins available in SQL are explained -- inner, left, right, and cross joins. A brief comparison to subqueries is also given. in previous SELECT queries, with the slight difference that column names are Write a query to return the first name of any singer who had an album released under the Warner Bros label. Hello!!! I need to merge three tables, one for each year (2015, 2016 and 2017), and continue to do so in the next years. And for that, i need a personalized column in the respective year. Is there a Power Query M function that allows the merged query to pull the "table name" or "query name" for

Standard SQL Query Syntax | BigQuery, name D Comma-separated list of the referenced primary key column names If the 74 (75) </fk-constraint-template</type-mapping 18.3.1.5 Aliases An alias header Otherwise, it'll use a LEFT JOIN and the isNull function: <sub-query-​true. A data access method (in your data acess layer) should know the name of the column. If you get a column by position you are relying on the table(s) always having your desired column in the proper position. That said, this is how you would do it. Assume here column 3 (column numbers in VFP start with 1 not 0)

MySQL Alias - Make The Queries More Readable, SELECT * FROM surveys JOIN species ON surveys.species_id The output of the JOIN command will have columns from first table plus the columns from so anywhere we would have used a field name in a non-join query, we can use table.colname . Can you translate them to SQL queries and give a suitable answer? In this Interview Question example, we are using the sys.column to get the column information, and sys.tables to get the database table names.-- Query to Find all Tables that Contain Column Name USE [SQL Tutorial] GO SELECT col.name AS [Column Name], tab.name AS [Table Name] FROM sys.columns col INNER JOIN sys.tables tab ON col.object_id = tab

Comments
  • It is entirely possible that this information is lost in certain transformations.
  • Just in case... What if, in the query, you use the (standard) backticks to define the column names instead of single quotes, like: RA_Min_1 AS `ran Min`?
  • have you tried ordering by the lable? like ORDER BY ran Min
  • Please provide the entire SELECT statement without the quotes and plusses needed for building the Java string.
  • Which "actual column name" do you need, and why?
  • This is by far the clearest answer.
  • In the absence of index, MySQL could use temp table to hold the output of the outer select for ordering and at that point the original column name might be getting lost.