If the reference value is NULL when creating the View table, I want to merge under different conditions.

We are currently creating a View table.

I am combining the table data as shown below, but if hoge2.player1_id is NULL, I would like to combine the player table with hoge.player1_id.

CREATE VIEWHOGE_view AS SELECT
 hoge.id,
 hoge.hoge_id,
 hoge2.game_id,
 games.game_category,
 games.tournament,
 hoge.game_result,
 hoge2.game_result,
 hoge.info,
 hoge2.player0_id,
 hoge2.player1_id,
 hoge2.player2_id,
 player0.status as player0_status,
 player1.status as player1_status,
 player2.status as player2_status,
 player0.name asplayer0_name,
 player1.name asplayer1_name,
 player2.name asplayer2_name,
 player0.first_name as player0_first_name,
 player1.first_name as player1_first_name,
 player2.first_name as player2_first_name,
 player0.team_id as player0_team_id,
 player1.team_id as player1_team_id,
 player2.team_id as player2_team_id,
 games.date
 FROM hoge
 JOIN HOGE 2
    ON hoge.hoge_id=hoge2.id
 JOIN GAMES
    ON hoge2.game_id=games.id
 JOIN PLAYERS AS PLAYER 0
    ON hoge2.player0_id=player0.id
 JOIN PLAYERS AS PLAYER1
    ON hoge2.player1_id=player1.id
 JOIN PLAYERS AS PLAYER 2
    ON hoge2.player2_id=player2.id
 JOIN TEAMS AS PLAYER1_team
    ON player1_team.id=player1.team_id
 JOIN teams AS player0_team
    ON player0_team.id=player0.team_id

When I created it as follows, I couldn't browse the View table anymore.
Do you know how to make this?
It would be very helpful if you could give me some advice.

Thank you for your cooperation.

JOIN player as player1
    ON (hoge2.player1_id=player.id AND hoge2.player1_id IS NOT NULL)
    OR hoge.player1_id=player.id

mysql sql

2022-09-30 21:51

1 Answers

If the reference value is NULL when creating the View table, I want to merge under different conditions.

How do you think about the following?

I tried the table definition of the person asked in a simplified model from a "combination" perspective.

[Table Definition]

MariaDB[work_db]>desc main table;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| f1 | int(11) | YES | | NULL | |
| f2 | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+

MariaDB[work_db]>desc Join 1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| note | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

MariaDB[work_db]>desc Join 2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| note | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

[Table data]

MariaDB[work_db]>select*from main table;
+------+------+------+
| id | f1 | f2 |
+------+------+------+
|    1 |    1 |    1 |
|    2 | 2 | NULL |
|    3 | NULL | 2 |
|    4 | NULL | NULL |
+------+------+------+

MariaDB[work_db]>select*from join 1;
+------+-------------------------------+
| id | note |
+------+-------------------------------+
|    1 | Join destination 1 data 1 |
|    2 | Join destination 1 data 2 |
+------+-------------------------------+

MariaDB[work_db]>select*from join 2;
+------+-------------------------------+
| id | note |
+------+-------------------------------+
|    1 | Join destination 2 data 1 |
|    2 | Join destination 2 data 2 |
+------+-------------------------------+

[Definition of view]

MariaDB[work_db]>create view view as
    ->select main table.idid, main table.f1f1, main table.f2f2, join destination 1.note note from main table join destination 1 on main table.f1 = join destination 1.id
    ->union
    ->select main table.idid, main table.f1f1, main table.f2f2, join destination 2.note note from main table join destination 2 on main table.f2 = join destination 2.id where main table.f1 is null
    ->;

[View Search Results]

MariaDB[work_db]>select*from view;
+------+------+------+-------------------------------+
| id | f1 | f2 | note |
+------+------+------+-------------------------------+
|    1 | 1 | 1 | Join Destination 1 Data 1 |
|    2 | 2 | NULL | Join destination 1 data 2 |
|    3 | NULL | 2 | Join Destination 2 Data 2 |
+------+------+------+-------------------------------+

2022-09-30 21:51

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656