Databases
StackOverflow2013
Postsdbo
POHow to union SELECT two tables with ids of both tables?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to union SELECT two tables with ids of both tables?
    text
    copied!<p>Ok, I have four tables:</p> <p>Table 1: "f_withholdings"</p> <p><img src="https://i.stack.imgur.com/yBbP1.png" alt="alt text"></p> <p>Table 2: "f_wh_list"</p> <p><img src="https://i.stack.imgur.com/UyPd4.png" alt="alt text"></p> <p>Table 3: "f_rpayments"</p> <p><img src="https://i.stack.imgur.com/6Nh2s.png" alt="alt text"></p> <p>Table 4: "f_rp_list"</p> <p><img src="https://i.stack.imgur.com/73XOQ.png" alt="alt text"></p> <p>Table 1 and Table 2 are connected with each other by <code>wh_id</code> field and Table 3 and Table 4 are connected by <code>rp_id</code> as seen in picture.</p> <p>I want to union select both tables into one, something like:</p> <pre><code>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` </code></pre> <p>and I get this:</p> <p><img src="https://i.stack.imgur.com/ZLuYS.png" alt="alt text"></p> <p>there is only one id field from first SELECT <code>wh_list_id</code> in result table, but no <code>rp_list_id</code></p> <p>I'd like to have both ids in result table, something like below:</p> <p><img src="https://i.stack.imgur.com/s2DG1.png" alt="alt text"></p> <p>Thanks!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload