Union

[George W Smith]

Hi All,

I am trying to use a union to get data in a table in my java class. In the mysql database the query executes perfectly.
However when ran in the java class it says it cannot find "user2"?

I have simplified the statement to the extreme(!!!) and it still says the same? Code is below any help would be great.

SELECT admin from private union SELECT user2 from private;

(user2 is a column in the database...)

[Jan Cumps]

Welcome to the Ranch, George!

Where does it say it can't find user2? When you run the query, or when you get the field?

It can help if you show that piece of code, and the exact error.

[George W Smith]

String statement = "SELECT admin from private union SELECT user2 from private";
prep = conn.prepareStatement(statement);
ResultSet rs = (ResultSet) prep.executeQuery();
while(rs.next()) {
temp1.setAdmin(rs.getString("admin"));
temp1.setMem2(rs.getString("user2"));
leagues.add(temp1);
}
conn.close();

Error:

Column 'user2' not found

The error is when I run it and it says no data to display.
There is a column user2 in the private table in the sql database, as the full statement works at command line.

[amit punekar]

Hello,
May be when you are executing the query you are connecting to different MYSQL instance than the one you are using from Java program.



Regards,
amit

[George W Smith]

No its the correct one alright as when I execute a query on just the select user2 with no union it works fine.
Is their a problem with unions and jdbc or am I making a mistake in it somewhere?

[Sam Falzon]

With a UNION clause you only get one column back. You probably want to select both columns rather than do a UNION since they are coming from the same table. Why are doing a UNION?
You may want to code your select this way:

SELECT admin, user2 from private.

[George W Smith]

Yes I know that, the long complex statement wasnt working. Therefore I am attempting to get a simpler one working with two basic selects.
Both statements give the same error.

[Jan Cumps]

How does the table look like, and what do you want to retrieve from it?

[Sam Falzon]

Your issue is really a SQL issue.

Here is a test table I created to mimic your data.

SQL> desc private
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 ADMIN                                                                             VARCHAR2(10)
 USER2                                                                             VARCHAR2(10)

SQL> select * from private;

ADMIN      USER2
---------- ----------
SMITH      FORD
ALLEN      MILLER
WARD       JOHNSON
JONES      MASON
MARTIN     YOUNG

5 rows selected.

Your SQL should produce this:

SQL> SELECT admin from private union SELECT user2 from private;

ADMIN
----------
ALLEN
FORD
JOHNSON
JONES
MARTIN
MASON
MILLER
SMITH
WARD
YOUNG

10 rows selected.

A few things to note:

Only 1 column is returned and it is called 'ADMIN' - hence Java is not aware of a column called user2.

There is no way to no which column (admin or user2 the data came from)

The Java to execute the SQL looks like this:

          String statement = "SELECT admin from private union SELECT user2 from private";
          ResultSet rs = prep.executeQuery (statement);
          while (rs.next()) {
            String userName = rs.getString("ADMIN");
            System.out.println("userName = " + userName);
         }

Output:

userName = ALLEN
userName = FORD
userName = JOHNSON
userName = JONES
userName = MARTIN
userName = MASON
userName = MILLER
userName = SMITH
userName = WARD
userName = YOUNG

[Jan Cumps]

Welcome to the JavaRanch, Sam!

[satish kumar jha]

nice but you get wrong
check this
================

select * from private

------

SELECT admin, " " as user2 from private union SELECT " " as admin, user2 from private

=================
both sql give same result
when you do union remember that all column name are same.

[Sam Falzon]

I guess it depends on your database. I am using Oracle and your SQLs do not produce identical results in Oracle.

SQL> select * from private;

ADMIN      USER2
---------- ----------
SMITH      FORD
ALLEN      MILLER
WARD       JOHNSON
JONES      MASON
MARTIN     YOUNG

5 rows selected.

Your original SQL used double-quotes, but this does not work in Oracle and gives a syntax error, so I changed it to single-quote:

SQL> SELECT admin, " " as user2 from private union SELECT " " as admin, user2 from private;
SELECT admin, " " as user2 from private union SELECT " " as admin, user2 from private
              *
ERROR at line 1:
ORA-00904: " ": invalid identifier

SQL> SELECT admin, ' ' as user2 from private union SELECT ' ' as admin, user2 from private;

ADMIN      USER2
---------- ----------
           FORD
           JOHNSON
           MASON
           MILLER
           YOUNG
ALLEN
JONES
MARTIN
SMITH
WARD

10 rows selected.

Your SQL will produce two columns, but it goes back to my original point that it was a SQL issue and the original poster was using the incorrect SQL. If he uses your SQL, that should fix his problem, but then he has to be aware that some admin and user2 variables will have blank values as the above SQL illustrates. Is this what the original poster wants? If no, then a simple SQL will do the job rather than a UNION:

SQL> SELECT admin, user2 from private;

ADMIN      USER2
---------- ----------
SMITH      FORD
ALLEN      MILLER
WARD       JOHNSON
JONES      MASON
MARTIN     YOUNG

5 rows selected.

The original poster should illustrate with example data of what is in the table 'private' and what he want to populate his objects with?

[Rupesh Mhatre]

in case of Original poster's original query of union the resultset what we get is of single column named as admin as select admin from private is the first query and there is no column named user2
thats why he was not getting that column in temp1.setMem2(rs.getString("user2"));

[satish kumar jha]

George W Smith wrote:No its the correct one alright as when I execute a query on just the select user2 with no union it works fine.
Is their a problem with unions and jdbc or am I making a mistake in it somewhere?

if you using mysql than read my post but check it in your complex statement it work not for just example.

Remember that when you try to union of two sql than both or more sql which you want to join have same column name so you use blank if necessary

also remember that use single quote if you use oracle. and in mysql both quotes are valid.