Monthly Archives: December 2008

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

Easy Way to Move Directory Through SSH

I used to mount my server’s home directory using sshfs to move quite a lot directory from and to my server’s machine. It was then, and this is now :


tar cf - directory_to_move | ssh user@hostname 'cd destination_dir; tar xf - .'

This command will do the same thing as scp but when paired with gzip or bzip you can add a nice compression (gzip or bzip) on the way. The directory structure and permission should be intact thanks to the benefits of tar.

This ssh thing never cease to amaze me.

Leave a comment

Filed under Ilmu, Orang bego punya kegiatan