How to combine SELECT two tables with identifiers of both tables?

Question

Ok, I have four tables:

Table 1: "f_withholdings"

Table 2: "f_wh_list"

Table 3: "f_rpayments"

Table 4: "f_rp_list"

Table 1 and Table 2 are linked by the wh_id field and table 3 and table 4 are connected by rp_id , as shown in the figure.

I want the join to select both tables into one, something like:

 SELECT `wh_list_id`, `wh_name` AS `name`, `wh_list_date` AS `date`, `wh_list_amount` AS `amount`, `wh_list_pending` AS `pending`, `wh_list_comment` AS `comment` FROM `f_wh_list` LEFT JOIN `f_withholdings` ON `f_wh_list`.`wh_id` = `f_withholdings`.`wh_id` UNION ALL SELECT `rp_list_id`, `rp_name` AS `name`, `rp_list_date` AS `date`, `rp_list_amount` AS `amount`, `rp_list_pending` AS `pending`, `rp_list_comment` AS `comment` FROM `f_rp_list` LEFT JOIN `f_rpayments` ON `f_rp_list`.`rp_id` = `f_rpayments`.`rp_id` 

and I get the following:

there is only one id field from the first SELECT wh_list_id in the result table, but not rp_list_id

I would like to have both identifiers in the result table, something like below:

Thanks!

Answer 1

Just select null as the column that is missing in each of them.

 SELECT `wh_list_id`, null AS `rp_list_id`, `wh_name` AS `name`, `wh_list_date` AS `date`, `wh_list_amount` AS `amount`, `wh_list_pending` AS `pending`, `wh_list_comment` AS `comment` FROM `f_wh_list` LEFT JOIN `f_withholdings` ON `f_wh_list`.`wh_id` = `f_withholdings`.`wh_id` UNION ALL SELECT null as `wh_list_id`, `rp_list_id`, `rp_name` AS `name`, `rp_list_date` AS `date`, `rp_list_amount` AS `amount`, `rp_list_pending` AS `pending`, `rp_list_comment` AS `comment` FROM `f_rp_list` LEFT JOIN `f_rpayments` ON `f_rp_list`.`rp_id` = `f_rpayments`.`rp_id` 

Answer 2

Just add the corresponding null column to each query (UNIONs are disconnected from the column position, they do not need names or aliases):

 SELECT `wh_list_id`, NULL, ... SELECT NULL, `rp_list_id`, ... 

It might be a little better to store the identifiers in one column and add a field that indicates which query the identifier is from ( SELECT 'wh_list', ... for example).