MySQL combine results from two tables with flag

Vladimir Štus 0
Viewed 35

Hello i am trying to solve this issue tru MYSQl without additional server side code.

1.event

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

2.charity

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

what i want is union this two tables into a new table with a flag indicating "isEvent" and "Charity payee" OR "Event payee & Charity payee" for example:

Note online_payee & payee_id are basically same field.

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

I have tried something like this but getting dupe results.

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)
mysql
1 Answers

To query two tables and create a flag that describes the type of record based on the tables information is located in, and search for records related to one account identification:

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
;

Outputs:

event_id payee_id type
2 330 charity
44 330 event

The WHERE online_payee_id = 330 clause restricts results to records associated with payee identification "330".

The details...

Given tables:

payee:

id
110
220
330

event:

id online_payee_id
1 110
2 220
2 330
44 330

charity:

id event_id payee_id
1 1 110
2 2 330

The query:

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*/
;

...(without the restricting WHERE clause) outputs:

event_id payee_id type
1 110 charity
2 330 charity
2 220 charity
44 330 event

First creating an ad hoc, temporary table, named events, that LEFT JOINs the event and charity tables. Using DISTINCT to create a table that contains a list of unique events. Also using IF which a check of the existence of a corresponding value in the charity table to determine whether it is a charitable event or not.

SELECT DISTINCT event.id event_id,
  IF (charity.event_id, "charity", "event") type
FROM event
LEFT JOIN charity ON event.id = charity.event_id
;

Temporary table output:

event_id type
1 charity
2 charity
44 event

Next naming the temporary table events and LEFT JOINing it with the event table, to get the payee_id information.

SELECT event_id, online_payee_id payee_id, type
FROM ( /* temporary table query goes here */ ) events
LEFT JOIN event ON events.event_id = event.id
;

The result is a list of unique event_id/payee_id combinations, and if the event is charitable.

-- create
CREATE TABLE payee (
  id INTEGER PRIMARY KEY
);

CREATE TABLE event (
  id INTEGER NOT NULL,
  online_payee_id INTEGER NOT NULL
);

CREATE TABLE charity (
  id INTEGER NOT NULL,
  event_id INTEGER NOT NULL,
  payee_id INTEGER NOT NULL
);

-- insert
INSERT INTO payee VALUES (110), (220), (330);
INSERT INTO event VALUES (1, 110), (2, 220), (2, 330), (44, 330);
INSERT INTO charity VALUES (1, 1, 110), (2, 2, 330);

-- fetch 
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
;

Try it here: https://onecompiler.com/mysql/3ygkeqb59

bloodyKnuckles0
Related