I've been making a query that is supposed to return catalogs in legacy deals where the catalog does not have associations with tables in the deal. Table is it's own module here. Think of it as almost like the missing table (referring to the table module here) associations in legacy. This is what my query looks like so far.
SELECT D.Deal, C.Catalog_Id, D.Deal_Template, T.Table_Id
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
INNER 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 DLTC.Table_Id = T.Table_Id
WHERE D.Deal_Template = 'Legacy Deal'
The sample output so far is
Deal_Id Catalog_Id Deal_Template Table_Id
-------------------------------------------------------
5687 1234 Legacy Deal 10
2987 4487 Legacy Deal 97
125 1888 Legacy Deal 1105
I've tried full joining, but I was told that it's not a good idea because it gets a lot of data that I do not need. I also tried setting Deal_Id to NULL, but that just returns nothing. Can someone help me out with this?
EDIT: some sample data for the tables
Deal
Deal Id Deal Template
--------------------------
123 Comment
54 Legacy Deal
Catalog
Catalog Id Catalog Name
----------------------------
12 Mouse movie
298 Space movie
Table
Table Id Table Name
----------------------------
1001 Parrot
2001 Space
Deal link table catalog
Parent Deal Id Catalog Id Ref Table Id
----------------------------------------------------
54 99 0 2
1109 61 0 1001
Expected Result:
Deal Id Catalog Id Deal Template Table Id
---------------------------------------------------------
9987 1088 Legacy Deal NULL
5550 4 Legacy Deal NULL