handle subquery with joins where same result is to be joined with multiple tables I have this query which is going through table B twice I want it go through tableB once as I should be able to select all necessary fields from a table once and use that selected result table in multiple joins.
 
    select tableA.aa,tableA.bb, tableK.address, tableP.address from tableA
        LEFT JOIN (
    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
) AS tableP ON tableP.ID = tableA.aa
LEFT JOIN (
    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
) AS tableK ON tableK.ID = tableA.bb
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;