How to append result from select to resultset?

I have the following tables:

Table 1 - NODES (I access it through a DB_LINK):

SITE_ID    LATITUDE    LONGITUDE
ABC123     21.018      -89.711
CDE456     20.35       -87.349
FGH789     20.258      -87.406
ABB987     18.54       -88.302
CFF546     18.542      -88.273
GHT553     18.52       -88.311

Table 2 - LINKS

ID   SITE_A    SITE_B   STATUS  NAME  LINK_TYPE REGION  ---> Many other fields
1    ABC123    GHT553
2    FGH789    CFF546     
3    CDE456    ABC123        
4    CFF546    GHT553

Table 3 - RESULT (This is what I want to achieve) - No matter the order

LINK_ID   SITE_A_ID   LAT_SITE_A   LON_SITE_A   SITE_B_ID   LAT_SITE_B   LON_SITE_B
1         ABC123      21.018       -89.711      GHT553      18.52        -88.311
2         FGH789      20.258       -87.406      CFF546      18.542       -88.273
3         CDE456      20.35        -87.349      ABC123      21.018       -89.711
4         CFF546      18.542       -88.273      GHT553      18.52        -88.311

(Plus several other fields, which means no trouble for me)

This is what I have tried:

SELECT RES2.*, SAM2.LATITUDE LAT_SITE_B, SAM2.LONGITUDE LON_SITE_B FROM(
    SELECT RES1.*, NOD.LATITUDE LAT_SITE_A, NOD.LONGITUDE LON_SITE_A FROM(
        SELECT ID, SITE_A, SITE_B, STATUS, NAME, LINK_TYPE FROM LINKS
            WHERE SITE_A IS NOT NULL AND SITE_B IS NOT NULL AND REGION IN (8,6)
         )RES1, NODES@NODES_DBLINK NOD WHERE RES1.SITE_A = NOD.SITE_ID
     )RES2, NODES@NODES_DBLINK NOD2 
WHERE RES2.SITE_B = NOD2.SITE_ID;

Until the SELECT RES1.\*, everything works fine, but when I add the SELECT RES2.\*, it takes too long without returning anything.

Solution

From the textual part of your question, this query will generate the result you want:

SELECT links.id AS link_id,
       node_a.site_id AS site_a_id,
       node_a.latitude AS lat_site_a,
       node_a.longitude AS lon_site_a,
       node_b.site_id AS site_b_id,
       node_b.latitude AS lat_site_b,
       node_b.longitude AS lon_site_b
  FROM links
 INNER JOIN nodes@nodes_dblink node_a ON (links.site_a = node_a.site_id)
 INNER JOIN nodes@nodes_dblink node_b ON (links.site_b = node_b.site_id)
 ORDER BY links.id;

From the query you posted it seems you have some other criteria to include too which might mean you want something more like this:

SELECT links.id AS link_id,
       node_a.site_id AS site_a_id,
       node_a.latitude AS lat_site_a,
       node_a.longitude AS lon_site_a,
       node_b.site_id AS site_b_id,
       node_b.latitude AS lat_site_b,
       node_b.longitude AS lon_site_b
  FROM links
 INNER JOIN nodes@nodes_dblink node_a ON (links.site_a = node_a.site_id)
 INNER JOIN nodes@nodes_dblink node_b ON (links.site_b = node_b.site_id)
 WHERE links.site_a IS NOT NULL
   AND links.site_b IS NOT NULL
   AND links.region IN (8, 6)
 ORDER BY links.id;

Hope it helps...

EDIT: If your DB link is an issue, try to return only the data you're going to need over the link in advance either by creating a view on the remote DB or a materialised view on the local DB. If that isn't practical then check the relative explain plans for the query above against this one and see if it is any better:

WITH node_data
  AS (SELECT site_id,
             latitude,
             longitude
        FROM nodes@nodes_dblink node
       WHERE EXISTS (SELECT 1
                       FROM links
                      WHERE links.site_a = node.site_id
                         OR links.site_b = node.site_id))
SELECT links.id AS link_id,
       node_a.site_id AS site_a_id,
       node_a.latitude AS lat_site_a,
       node_a.longitude AS lon_site_a,
       node_b.site_id AS site_b_id,
       node_b.latitude AS lat_site_b,
       node_b.longitude AS lon_site_b
  FROM links
 INNER JOIN node_data node_a ON (links.site_a = node_a.site_id)
 INNER JOIN node_data node_b ON (links.site_b = node_b.site_id)
 WHERE links.site_a IS NOT NULL
   AND links.site_b IS NOT NULL
   AND links.region IN (8, 6)
 ORDER BY links.id;