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