4

Ok, I have four tables:

Table 1: "f_withholdings"

alt text

Table 2: "f_wh_list"

alt text

Table 3: "f_rpayments"

alt text

Table 4: "f_rp_list"

alt text

Table 1 and Table 2 are connected with each other by wh_id field and Table 3 and Table 4 are connected by rp_id as seen in picture.

I want to union 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 this:

alt text

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

I'd like to have both ids in result table, something like below:

alt text

Thanks!

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Darkhan
  • 1,258
  • 2
  • 13
  • 21

2 Answers2

6

Just select null as the column that is missing from each.

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`
tvanfosson
  • 524,688
  • 99
  • 697
  • 795
  • 1
    +1: Same comment as to Dmitri about about column aliases - most databases don't need the explicit alias in statements defined after the first statement of a UNION. – OMG Ponies Nov 22 '10 at 00:47
1

Just add a corresponding null column to each query (UNIONs work off of column position, they don't care about names or aliases):

SELECT
`wh_list_id`,
NULL,
...

SELECT
NULL,
`rp_list_id`,
...

It may be slightly better to keep the ids in one column, and add a field that specifies which query the id is from (SELECT 'wh_list', ... for example).

Dmitri
  • 8,999
  • 5
  • 36
  • 43
  • +1: If you want to reference a column, aliases matter ;) The column reference is defined by the first of the UNION'd statements. – OMG Ponies Nov 22 '10 at 00:46
  • Only if you want to reference it by name :) (which, fair enough, you should) – Dmitri Nov 22 '10 at 00:50