MySQL使用flag将两个表的结果组合在一起

Question

你好，我正在尝试在没有额外服务器端代码的情况下解决这个问题tru MYSQl。

1.事件

+--------------------------+-------------------+
|id                        | online_payee_id   |   
+--------------------------+-------------------+
| 1                        | 110               |
| 2                        | 330               |
|44 (not in charity table) | 330               |
+---+------------------------------------------+

2.慈善

+---+------------+------------+
|id | event_id   |  payee_id  |
+---+------------+------------+
| 1 | 1          |    330     |
| 2 | 2          |    330     |
+---+------------+------------+

我想要的是将这两个表合并为一个新的表，该表具有指示"；isEvent"以及"；慈善收款人；OR"；事件收款人&慈善收款人；例如：

注意online_payee&payee_id基本上是相同的字段。

+---+------------+----------------------------+
|id | event_id   |    flag                    |
+---+------------+----------------------------+
| 1 | 1          | Charity payee              |
| 1 | 2          | Event payee & Charity payee|
| 2 | 44         | IsEvent                    |
+---+------------+----------------------------+

我试过这样的方法，但结果都是骗人的。

SELECT 
COALESCE(EV.id, EV2.id) AS `id`, 
IF(EV.online_payee_id AND EC.payee_id,"Event payee / Charity payee",  
IF(EV.online_payee_id,"Event payee","Charity payee")) 
AS `type` 
FROM `payee` AS `P` 
LEFT JOIN `event` AS `EV` ON P.id = EV.online_payee_id 
LEFT JOIN `charity` AS `EC` ON P.id = EC.payee_id
LEFT JOIN `event` AS `EV2` ON EC.event_id = EV2.id 
WHERE (P.id = 330) 
AND (EC.payee_id IS NOT NULL OR EV.online_payee_id IS NOT NULL)

Answer 1

要查询两个表并根据信息所在的表创建一个描述记录类型的标志，并搜索与一个帐户标识相关的记录：

SELECT event_id, online_payee_id payee_id, type
FROM (
SELECT DISTINCT event.id event_id,
IF (charity.event_id, "charity", "event") type
FROM event
LEFT JOIN charity ON event.id = charity.event_id
) events
LEFT JOIN event ON events.event_id = event.id
WHERE online_payee_id = 330
;

输出：

慈善机构事件

event_id	payee_id	类型
2	330
44		330