Tag Archives: sql

Firebird’s UNION use for multipe SELECT result

I would like to post this one just in case I’ll need it someday in the future.

UNION can be used to ‘append’ multiple SELECT result into one stream of rows. This is not a JOIN. There are few situations where a UNION should be used to get a certain result. Here’s one:

I’ve got a table, a view to be exact, like this :


V_EMPLOYEERELATION
+------------+-------------+----------+
|  EMPLOYEE  |  EMPLOYEE_2 | RELATION |
+------------+-------------+----------+
|  101       |  203        | 45       |
+------------+-------------+----------+
|  104       |  108        | 40       |
+------------+-------------+----------+
|  102       |  79         | 45       |
+------------+-------------+----------+

As you can see this is a table that stores relationship between employees. The task is to get all employee entries having a certain Relation ID.

Right, now here’s the query:


SELECT EMPLOYEE AS "ID" FROM V_EMPLOYEERELATION WHERE RELATION = %d
UNION
SELECT EMPLOYEE_2 AS "ID" FROM V_EMPLOYEERELATION WHERE RELATION = %d';

This query will return rows of employee ID satisfying a relation ID. If you know Python or Delphi, you might know the formatting string there. For people who don’t know, the %d is a replacement for an integer value. In this example I’m using integer as foreign key to a RELATION table.

One can use this SQL query like this:


const
  SQUERY_GETEMP = 
  'SELECT EMPLOYEE AS "ID" FROM V_EMPLOYEERELATION WHERE RELATION = %d ' +
  'UNION ' +
  'SELECT EMPLOYEE_2 AS "ID" FROM V_EMPLOYEERELATION WHERE RELATION = %d';

...
// RunQuery is a function that takes string SQL query and a dataset, returning an integer
if ( RunQuery(Format(SQUERY_GETEMP, [iRelationID, iRelationID]), Table1) = 0 ) then
begin
  while (not Table1.EOF) do
   ...

Please note that UNIONs requires each ‘source-stream’ (a.k.a SELECT statements) to have the same fields number, type and size.

Leave a comment

Filed under Ilmu