asked on

How to perform a nested outer join?

I have three tables (table_A, table_B, and table_C let's call them). I need to use B and C to reduce table_A down to only those applications that are not already related to app#147 in table_C and is only of type_category 2 in table_B. So far this is what I have (see below) and it works up until the final line (the filter that uses table_B works). When, however, I include the last line I receive an empty set of records. When I use the code below, and leave out the last line, I get 90 records returned (89 records is what I'm after).

Table_C has a record that relates application #147 (master_app_id) to application #118 (linked_app_id). I'm hoping the last line of code will remove application #118 from my query results. I know I will later relate several more apps to App#147 so I don't want to hard-code #118 in the code...I want to just filter out all apps that are already related to app#147.

SELECT DISTINCT a.app_id, a.Name, a.app_type FROM table_A a
LEFT OUTER JOIN table_B b
ON a.app_type = b.app_type
LEFT OUTER JOIN table_C c
ON a.app_id = c.linked_app_id
WHERE b.app_type_category = 2 
AND c.master_app_id = 147

Open in new window

Guy Hengel [angelIII / a3]

what about this:

SELECT DISTINCT a.app_id, a.Name, a.app_type FROM table_A a
LEFT OUTER JOIN table_B b
ON a.app_type = b.app_type
AND b.app_type_category = 2 
LEFT OUTER JOIN table_C c
ON a.app_id = c.linked_app_id
AND c.master_app_id = 147

Open in new window

David L. Hansen

ASKER

Hmm.. now all app_types are being returned. 155 rows come back from that query. Table_A has 155 total rows to start with. Confusing....

David L. Hansen

ASKER

Angel, this works. However, it uses a nested select statement. Not that that's bad, but I really want to become more proficient with the JOIN statements.

SELECT DISTINCT a.App_ID, a.Name, a.App_Type
FROM         Table_A a LEFT OUTER JOIN
             Table_B b ON a.App_Type = b.App_Type LEFT OUTER JOIN
             Table_C c ON a.App_ID = c.Master_App_ID
WHERE     (b.App_Type_Category = 2) AND (a.App_ID NOT IN
          (SELECT Linked_App_ID
           FROM Application_Links
           WHERE (Master_App_ID = 147))) 
AND a.App_ID <> 147
ORDER BY a.Name

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David L. Hansen

ASKER

Yes! Perfect!

Thanks.

By the way, I've always been able to follow the different sets of data through nested queries in my head. However, with 'join-queries' the logic fails me. I just can't see the logic flow of the separate sets of data through the statement. Can you enlighten me?

I'd be happy to post this separately if you'd like.

Guy Hengel [angelIII / a3]

the NOT EXISTS (just like a EXISTS() or IN()) shown/used here is correlated subquery.
which means, that the subquery is not executed once for the entire query, but once per row of the main dataset.
this does not mean that it will be inefficient, because the internal sql engine will actually try to do some hash-merge routines, if there are proper indexes, that will work "like" a join... just a bit different and with eventually different results, just as defined by the syntax...

David L. Hansen

ASKER

Thanks.