Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 783
  • Last Modified:

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

0
David L. Hansen
Asked:
David L. Hansen
  • 4
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
David L. HansenProgrammer AnalystAuthor Commented:
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....
0
 
David L. HansenProgrammer AnalystAuthor Commented:
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

0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, you want this:
SELECT DISTINCT a.App_ID, a.Name, a.App_Type
FROM Table_A a 
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.Master_App_ID
WHERE a.App_ID <> 147
 AND NOT EXISTS (SELECT NULL 
                   FROM Application_Links al
                  WHERE al.Linked_App_ID = a.App_ID
                    AND al.Master_App_ID = 147
                 )
ORDER BY a.Name

Open in new window

0
 
David L. HansenProgrammer AnalystAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
0
 
David L. HansenProgrammer AnalystAuthor Commented:
Thanks.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now