如何创建这个正确的连接

odopli94 于 2021-06-17 发布在 Mysql

关注(0)|答案(3)|浏览(355)

我需要创建一个视图，我把所有的交易，我需要关联所有的操作。如果transactionoperation中没有操作，我需要输入结果。

Transaction( transaction id,transaction_name)

Operation(id_operation,operation_name);

TransactionOperatation(id_transaction,id_operation); // represents all transaction to be used

因此，观点是：

Create view title AS select
t.id_transaction as id_transaction,
t.name_transaction as name_transaction,
o.id_operation as id_operation,
o.name_operation as name_operation,

from ((TransactionOperation to 
inner join transaction t on t.id_transaction=to.id_transaction)
rigth join operation o on o.id_operation=to.id_operation

问题是助理没有按我所希望的那样工作。这必须是结果，例如：

Operation id_operation operation_name
           1            test
           2            home
transaction id_transaction  transaction_name
               1              scope
               2              pool

TransactionOperation id_operation operation_name id_transaction transaction name:
                         1            test          1             scope     
                         1            test          2             pool

在本例中，我需要为每个事务获取所有操作。id=2的操作不会出现在transactionoperation中，但必须出现在结果中。我想要的结果是

id_operation operation_name id_transaction transaction name:
   1            test          1              scope
   2            home          1              scope
   1            test          2              pool
   2            home          2              pool

有人能帮我吗？

sql mysql View

来源：https://stackoverflow.com/questions/53479432/how-create-this-right-join

3条答案

按热度按时间

ftf50wuq1#

你似乎只是想要一个 cross join :

select o.id_operation, o.operation_name, t.id_transaction, t.transaction_name
from Transaction t cross join
     Operation o;

不需要子查询或其他类型的查询 join s。

赞(0）回复(0）举报 2021-06-18

s6fujrry2#

我会从连接表开始加入 TransactionOperation ，然后左键连接到其他两个表：

CREATE VIEW title AS
SELECT
    COALESCE(t.id_transaction, 'NA')   AS id_transaction,
    COALESCE(t.name_transaction, 'NA') AS name_transaction,
    COALESCE(o.id_operation, 'NA')     AS id_operation,
    COALESCE(o.name_operation, 'NA')   AS name_operation
FROM TransactionOperation to
LEFT JOIN transaction t
    ON t.id_transaction = to.id_transaction
LEFT JOIN operation o
    ON o.id_operation = to.id_operation

当前方法的问题是在连接表和 transaction table。这意味着任何未连接到 transaction 在他们还没来得及加入到游戏中之前，表将被删除 operation table。到处使用左连接可以解决这个问题。

赞(0）回复(0）举报 2021-06-18

pvabu6sv3#

你在试图得到所有可能的组合 transaction 以及 operation ，尽管没有在联接表中定义它们之间的关系。这可以通过笛卡尔积来实现( CROSS JOIN )在两个表的唯一值之间：

CREATE VIEW title AS
SELECT
  t.*, o.*
FROM 
  (SELECT DISTINCT id_transaction, transaction_name FROM transaction) AS t
CROSS JOIN
  (SELECT DISTINCT id_operation, operation_name FROM operation) AS o

赞(0）回复(0）举报 2021-06-17

我来回答

如何创建这个正确的连接

3条答案

相关问题

热门标签

最新问答