handle subquery with joins where same result is to be joined with multiple tables
I have written same query subquery twice


Create VIEW (created once, it exists in database until ALTER or DROP, like tables):

CREATE VIEW my_subquery AS
SELECT tableB.ID
     , tableB.Address
--   , tableD.Name
--   , tableC.tableCName
--   , tableC.Country
FROM
    tableB
INNER JOIN tableD ON tableB.tableDID = tableD.ID AND tableD.Deleted = 0
INNER JOIN tableC ON tableD.tableCID = tableC.ID
WHERE
    tableB.Deleted = 0;


and then use it in Your query like a common table:

SELECT tableA.aa, tableA.bb, tableK.address, tableP.address 
FROM tableA
LEFT JOIN my_subquery AS tableP ON tableP.ID = tableA.aa
LEFT JOIN my_subquery AS tableK ON tableK.ID = tableA.bb;