1

We have multiple tables that contain "static" key/value pairs, which are currently pulled using multiple SQL (MSSQL) queries. Is it possible to pull all of this data in one SQL query, so that we can reference each column key and column value in a single result set? So for example:

TABLE_ONE
id, my_key_name, my_value_name

TABLE_TWO
id, my_other_key_name, my_other_value_name

Keep in mind that the column names for the key and for the value are different for each table. Basically, we're trying to consolidate multiple calls into one. Is this a situation where we'll have to have multiple Java ResultSets and we'll just need to do the combining in the code?

nickytonline
  • 6,855
  • 6
  • 42
  • 76
  • if you can do it in sql, you can do it in java with sql – NimChimpsky Aug 23 '11 at 16:54
  • I think Union operator, http://www.w3schools.com/sql/sql_union.asp. Not an expert, but interesting question so I looked a bit. And the AS operator to get your columns to the same names. – david van brink Aug 23 '11 at 16:56

2 Answers2

4

How about

SELECT id, my_key_name, my_value_name
FROM   TABLE_ONE
UNION
SELECT id, my_other_key_name, my_other_value_name
FROM   TABLE_TWO

?

See: UNION

NullUserException
  • 83,810
  • 28
  • 209
  • 234
  • Ah, I mistakenly thought the column names need to match. Interestingly, the columns just get names from the first SELECT statement, and the others merely need to me the same number of columns & matching types. Huh! – david van brink Aug 23 '11 at 17:23
  • @NullUserException Looking at Adam's response below, is that consistent with how you would expect to retrieve the union in the result set? I also don't want duplicate values overridden, which I thought UNION did... do I need to use UNION ALL? –  Aug 23 '11 at 19:28
  • @Brian `UNION` will get rid of duplicates, you'd need `UNION ALL` if you want everything. – NullUserException Aug 23 '11 at 19:31
  • @Brian This will return a single result set containing the results from both SELECT statements (assuming you use UNION ALL). The problem here is that you'll have no real way of knowing which results are from which table. That might be what you want, or it might not. It depends on how you're using the data after you get it. – Adam Jaskiewicz Aug 23 '11 at 20:09
  • @Adam I didn't think about that... I guess there would be no way to know which result set I was getting... I wonder if instead of a getMoreResults there would be some sort of call based upon the table name? –  Aug 23 '11 at 20:17
  • If you UNION the two SELECT statements together like this, you'll get back one ResultSet, not two. You won't be able to tell which rows came from TABLE_ONE and which rows came from TABLE_TWO, unless I'm missing something. If you don't care which table they came from, this is probably a better answer. – Adam Jaskiewicz Aug 23 '11 at 20:39
1

I don't know if you can do it in one Statement (by separating the queries with semicolons), but you can certainly create a stored procedure that returns multiple result sets.

After calling getResultSet, you can use the getMoreResults method (from java.sql.Statement) to move to the next ResultSet. It closes the current ResultSet, so you'll need to get whatever data you need to out of the first ResultSet before calling getMoreResults and getting the next ResultSet from the Statement.

Adam Jaskiewicz
  • 10,934
  • 3
  • 34
  • 37
  • Is this behavior consistent with most drivers? –  Aug 23 '11 at 18:38
  • I'm not sure how the various drivers handle it, it's been a while since I've worked directly with JDBC. Any driver that doesn't implement the behavior in the java.sql.Statement interface contract, though, would be a broken driver. – Adam Jaskiewicz Aug 23 '11 at 19:54