SELECT
i_completion.id, i_completion.id_schet as main_tb_id, 'completion' as table_name, i_completion.number, i_completion.date, i_completion.summa_goods,
i_invoice.id as id2, 'invoice' as table_name2, i_invoice.number as number2, i_invoice.date as date2, i_invoice.summa_goods as summa_goods2,
NULL, NULL, NULL, NULL, NULL
FROM i_schet_na_oplatu
INNER JOIN i_completion ON i_completion.id = i_schet_na_oplatu.id_completion
LEFT JOIN i_invoice ON i_invoice.id = i_completion.id_invoice
WHERE i_schet_na_oplatu.id IN($main_doc_id) AND i_completion.id = i_schet_na_oplatu.id_completion AND i_schet_na_oplatu.id_user = $user_id
UNION ALL -- i_tmz on i_schet
SELECT
i_tmz.id, i_tmz.id_schet as main_tb_id, 'tmz' as table_name, i_tmz.number, i_tmz.date, i_tmz.summa_goods,
i_invoice.id as id2, 'invoice' as table_name2, i_invoice.number as number2, i_invoice.date as date2, i_invoice.summa_goods as summa_goods2,
i_cash_order.id as id3, 'cash' as table_name3, i_cash_order.number as number3, i_cash_order.date as date3, i_cash_order.summa_goods as summa_goods3
FROM i_schet_na_oplatu
INNER JOIN i_tmz ON i_tmz.id = i_schet_na_oplatu.id_tmz
LEFT JOIN i_invoice ON i_invoice.id = i_tmz.id_invoice
LEFT JOIN i_cash_order ON i_cash_order.id = i_tmz.id_cash
WHERE i_schet_na_oplatu.id IN($main_doc_id) AND i_tmz.id = i_schet_na_oplatu.id_tmz AND i_schet_na_oplatu.id_user = $user_id
UNION ALL -- i_invoice on i_schet
SELECT
i_invoice.id, i_invoice.id_schet as main_tb_id, 'invoice' as table_name, i_invoice.number, i_invoice.date, i_invoice.summa_goods,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
FROM i_schet_na_oplatu
INNER JOIN i_invoice ON i_invoice.id = i_schet_na_oplatu.id_invoice
WHERE i_schet_na_oplatu.id IN($main_doc_id) AND i_invoice.id = i_schet_na_oplatu.id_invoice AND i_schet_na_oplatu.id_user = $user_id
UNION ALL -- i_cash on i_schet
SELECT i_cash_order.id, i_cash_order.id_schet as main_tb_id, 'cash' as table_name, i_cash_order.number, i_cash_order.date, i_cash_order.summa_goods,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL FROM i_schet_na_oplatu
INNER JOIN i_cash_order ON i_cash_order.id = i_schet_na_oplatu.id_cash
WHERE i_schet_na_oplatu.id IN($main_doc_id) AND i_cash_order.id = i_schet_na_oplatu.id_cash AND i_schet_na_oplatu.id_user = $user_id
UNION ALL -- i_payorder_in on i_schet
SELECT i_payorder_in.id, i_payorder_in.id_schet as main_tb_id, 'payorder_in' as table_name, i_payorder_in.number, i_payorder_in.date, i_payorder_in.summa_goods,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL FROM i_schet_na_oplatu
INNER JOIN i_payorder_in ON i_payorder_in.id = i_schet_na_oplatu.id_payorder_in
WHERE i_schet_na_oplatu.id IN($main_doc_id) AND i_payorder_in.id = i_schet_na_oplatu.id_payorder_in AND i_schet_na_oplatu.id_user = $user_id
UNION ALL -- i_invoice on i_completion
SELECT
i_invoice.id, i_invoice.id_completion as main_tb_id, 'invoice' as table_name, i_invoice.number, i_invoice.date, i_invoice.summa_goods,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
FROM i_completion
INNER JOIN i_invoice ON i_invoice.id = i_completion.id_invoice
WHERE i_completion.id IN($main_doc_id) AND i_invoice.id = i_completion.id_invoice AND i_completion.id_user = $user_id
UNION ALL -- i_invoice on i_tmz
SELECT
i_invoice.id, i_invoice.id_tmz as main_tb_id, 'invoice' as table_name, i_invoice.number, i_invoice.date, i_invoice.summa_goods,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
FROM i_tmz
INNER JOIN i_invoice ON i_invoice.id = i_tmz.id_invoice
WHERe i_tmz.id IN ($main_doc_id) AND i_invoice.id = i_tmz.id_invoice AND i_tmz.id_user = $user_id
UNION ALL -- i_cash_order on i_tmz
SELECT
i_cash_order.id, i_cash_order.id_tmz as main_tb_id, 'cash' as table_name, i_cash_order.number, i_cash_order.date, i_cash_order.summa_goods,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL FROM i_tmz
INNER JOIN i_cash_order ON i_cash_order.id = i_tmz.id_cash
WHERE i_tmz.id IN($main_doc_id) AND i_cash_order.id_tmz = i_tmz.id_cash AND i_tmz.id_user = $user_id
UNION ALL -- i_statement on i_account_cash
SELECT
i_statement.id, i_statement.id_account as main_tb_id, 'statement' as table_name, i_statement.number, i_statement.date, i_statement.summa_goods,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL FROM i_account_cash
INNER JOIN i_statement ON i_account_cash.id = i_statement.id_account
WHERE i_account_cash.id IN($main_doc_id) AND i_account_cash.id_statement = i_statement.id AND i_statement.id_user = $user_id
Простой
