Combining two SQL result to form one result set in a row with different column names

Question

As I need to combine two SQL results forming one row in the result set.

I tried to make use of union function. But results in two row in the result set with oponent_* column leaving opposer_* column completely. But it should include both the column in the result set. Here is the below piece of code

SELECT DISTINCT
  `team_member`.`Team_ID` AS oponent_Team_ID,
  `team`.`Founder_ID` AS oponent_Founder_ID,
  `team`.`Team_Logo` AS oponent_Team_Logo,
  `team`.`team_Name` AS oponent_Founder_ID,
  `teams_game_match`.`team_1_id` AS oponent_team_1_id,
  `teams_game_match`.`team_2_id` AS oponent_team_2_id,
  `teams_game_match`.`game_time` AS oponent_game_time,
  `teams_game_match`.`game_date` AS oponent_game_date,
  `teams_game_match`.`game_name` AS oponent_game_name,
  `teams_game_match`.`accept` AS oponent_accept
FROM
  `team_member`
  JOIN `team`
    ON `team_member`.`Team_ID` = `team`.`Team_ID`
  JOIN `teams_game_match`
    ON `teams_game_match`.`team_2_id` = `team`.`Team_ID`
WHERE `teams_game_match`.`team_1_id` = '11'

UNION

SELECT DISTINCT
  `team_member`.`Team_ID` AS opposer_Team_ID,
  `team`.`Founder_ID` AS opposer_Founder_ID,
  `team`.`Team_Logo` AS opposer_Team_Logo,
  `team`.`team_Name` AS opposer_Founder_ID,
  `teams_game_match`.`team_1_id` AS opposer_team_1_id,
  `teams_game_match`.`team_2_id` AS opposer_team_2_id,
  `teams_game_match`.`game_time` AS opposer_game_time,
  `teams_game_match`.`game_date` AS opposer_game_date,
  `teams_game_match`.`game_name` AS opposer_game_name,
  `teams_game_match`.`accept` AS opposer_accept
FROM
  `team_member`
  JOIN `team`
    ON `team_member`.`Team_ID` = `team`.`Team_ID`
  JOIN `teams_game_match`
    ON `teams_game_match`.`team_1_id` = `team`.`Team_ID`
WHERE `teams_game_match`.`team_2_id` = '11'

Actual Result:

+-----------------+--------------------+-------------------+--------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+
| oponent_Team_ID | oponent_Founder_ID | oponent_Team_Logo | oponent_Founder_ID | oponent_team_1_id | oponent_team_2_id | oponent_game_time | oponent_game_date | oponent_game_name | oponent_accept |
+-----------------+--------------------+-------------------+--------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+
|               4 |                  1 | 486396439.png     | Nish               |                11 |                 4 | 1:30am            | 2019-06-28        | Battlefield 4     |              0 |
|              13 |                  7 | 557132285.png     | BFM                |                13 |                11 | 1:30am            | 2019-07-12        | FIFA 17           |              1 |
+-----------------+--------------------+-------------------+--------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+
2 rows in set (0.00 sec)

Expected Result:

+-----------------+--------------------+-------------------+--------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+-----------------+--------------------+-------------------+--------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+
| oponent_Team_ID | oponent_Founder_ID | oponent_Team_Logo | oponent_team_Name  | oponent_team_1_id | oponent_team_2_id | oponent_game_time | oponent_game_date | oponent_game_name | oponent_accept | opposer_Team_ID | opposer_Founder_ID | opposer_Team_Logo | opposer_team_Name  | opposer_team_1_id | opposer_team_2_id | opposer_game_time | opposer_game_date | opposer_game_name | opposer_accept |
+-----------------+--------------------+-------------------+--------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+-----------------+--------------------+-------------------+--------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+
|               4 |                  1 | 486396439.png     | Team 1             |                11 |                 4 | 1:30am            | 2019-06-28        | Battlefield 4     |              0 |              13 |                  7 | 557132285.png     | Team 3             |                13 |                11 | 1:30am            | 2019-07-12        | FIFA 17           |              1 |
+-----------------+--------------------+-------------------+--------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+-----------------+--------------------+-------------------+--------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+
1 rows in set (0.00 sec)

SQL Fiddle Demo

Required to obtain result with one row combined with different column names in the result set. Let me know are there any other functions apart from it.

Finally, [Expected Result](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d3021ad94394ad9779db35d05027efe9) turns out! — Nɪsʜᴀɴᴛʜ ॐ, Jul 05 '19 at 07:04

Fahmi · Accepted Answer · 2019-07-04T13:02:57.933

You can try using case when

DEMO

select max(case when flag=1 then   oponent_Team_ID end) as oponent_Team_ID,
max(case when flag=0 then   oponent_Team_ID end) as opposer_Team_ID

from
(
SELECT DISTINCT
  `team_member`.`Team_ID` AS oponent_Team_ID,
  `team`.`Founder_ID` AS oponent_Founder_ID,
  `team`.`Team_Logo` AS oponent_Team_Logo,
  `team`.`team_Name` AS oponent_Founder_Name,
  `teams_game_match`.`team_1_id` AS oponent_team_1_id,
  `teams_game_match`.`team_2_id` AS oponent_team_2_id,
  `teams_game_match`.`game_time` AS oponent_game_time,
  `teams_game_match`.`game_date` AS oponent_game_date,
  `teams_game_match`.`game_name` AS oponent_game_name,
  `teams_game_match`.`accept` AS oponent_accept, 1 as flag
FROM
  `team_member`
  JOIN `team`
    ON `team_member`.`Team_ID` = `team`.`Team_ID`
  JOIN `teams_game_match`
    ON `teams_game_match`.`team_2_id` = `team`.`Team_ID`
WHERE `teams_game_match`.`team_1_id` = '11'

UNION

SELECT DISTINCT
  `team_member`.`Team_ID` AS opposer_Team_ID,
  `team`.`Founder_ID` AS opposer_Founder_ID,
  `team`.`Team_Logo` AS opposer_Team_Logo,
  `team`.`team_Name` AS opposer_Founder_ID,
  `teams_game_match`.`team_1_id` AS opposer_team_1_id,
  `teams_game_match`.`team_2_id` AS opposer_team_2_id,
  `teams_game_match`.`game_time` AS opposer_game_time,
  `teams_game_match`.`game_date` AS opposer_game_date,
  `teams_game_match`.`game_name` AS opposer_game_name,
  `teams_game_match`.`accept` AS opposer_accept,0
FROM
  `team_member`
  JOIN `team`
    ON `team_member`.`Team_ID` = `team`.`Team_ID`
  JOIN `teams_game_match`
    ON `teams_game_match`.`team_1_id` = `team`.`Team_ID`
WHERE `teams_game_match`.`team_2_id` = '11'
)A

@Nishanthॐ, check here https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=660fd78fa9d8a846879a7ca7df88fd83 — Fahmi, Jul 04 '19 at 13:03
[Check Here](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=702789a7d3b2434a6eb16e766a18e03d) Getting Error as `Unknown column 'opposer_team_1_id' in 'field list'` — Nɪsʜᴀɴᴛʜ ॐ, Jul 04 '19 at 13:22
@Nishanthॐ, chk now - https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a78d7ac7b2ac2bcab2964eb3b379a644 when you do union then no need to define column name for 2nd query, so that's why it was giving error — Fahmi, Jul 04 '19 at 13:25

Combining two SQL result to form one result set in a row with different column names

SQL Fiddle Demo

1 Answers1