How can I Exclude All Ids Using a Certain Condition in a Join?

Viewed 26

I have an issue with my SQL query right now. I'm supposed to exclude any catalog id that shows up in the "Sales Deal Catalogue Allocation (Local Currency)" table template. I'm not allowed to use subqueries in order to solve this problem. I must only use filters and joins. This is what I have so far:

SELECT C.Catalog_Id, D.Deal_Id, D.Deal_Status, D.Deal_Template, T.Table_Id, T.Table_Template
FROM Deal D
INNER JOIN Deal_Link_Catalog DLC
  ON DLC.Parent_Deal_Id = D.Deal_Id
INNER JOIN [Catalog] C
  ON C.Catalog_Id = DLC.Catalog_Id
LEFT JOIN Deal_Link_Table_Catalog DLTC
  ON DLTC.Catalog_Id = C.Catalog_Id
  AND DLTC.Parent_Deal_Id = D.Deal_Id
INNER JOIN [Table] T
  ON T.Table_Id = DLTC.Table_Id 
WHERE T.Table_Template <> 'Sales Deal Catalogue Allocation (Local Currency)'

My issue is arising from data that exists in both the local currency table template and other table templates. For example, 19738 is a catalog id that shows up in the output. This is an issue because it exists in the local currency table and others. I'm wondering how I can exclude all ids from that table template in general. Deal is the parent of catalog in the deal link catalog table. Deal is the parent of table, and table is the parent of catalog in the deal link table catalog table. I would like advice on what I can do to fix my query.

Sample Data of Sales Deal Catalogue Allocation (Local Currency) Template:

Catalog Id  Deal Id  Deal Status  Deal Template   Table Id  Table Template
---------------------------------------------------------------------------
18            15         Open          Require       9      Local Currency

Sample Data of GBP Table (Other Table Template):

Catalog Id  Deal Id  Deal Status  Deal Template   Table Id  Table Template
---------------------------------------------------------------------------
18            15         Open          Require        9         GBP
1             90         Open         Require         5         GBP

I don't want to output the row with a Catalog Id of 18, for example. It shows up in the local currency table. I don't want anything from there.

Expected Output:

Catalog Id  Deal Id  Deal Status  Deal Template   Table Id  Table Template
---------------------------------------------------------------------------
1             90         Open         Require         5         GBP
0 Answers
Related