0

I'm trying to remove the duplicate combinations from my cross join select statement, I have followed this example but the duplicated combination is still there, it even mixes the credit_id and loan_id. How am I supposed to do this? My code

SELECT DISTINCT 
CASE WHEN `credit`.`id`<=`loan`.`id` THEN `credit`.`id` ELSE `loan`.`id` END AS `credit_id`,
CASE WHEN `credit`.`id`<=`loan`.`id` THEN `loan`.`id` ELSE `credit`.`id` END AS `loan_id`
FROM
(SELECT `id`,`city` as `City with credit`,`fishing_segment` FROM `data` 
WHERE `city` LIKE '%Credit') AS `credit`
CROSS JOIN
(SELECT `id`,`city` as `City with loan`,`fishing_segment` FROM `data` 
WHERE `city` LIKE 'Loan%') AS `loan`;

The result

+-----------+---------+
| credit_id | loan_id |
+-----------+---------+
|         1 |       2 |
|         2 |       3 |
|         2 |       5 |
|         1 |       4 |
|         3 |       4 |
|         4 |       5 |
|         1 |       6 |
|         3 |       6 |
|         5 |       6 |
+-----------+---------+

The result that I want

+-----------+---------+
| credit_id | loan_id |
+-----------+---------+
|         1 |       2 |
|         3 |       4 |
|         5 |       6 |
+-----------+---------+

Here's the FIDDLE

ImBaldingPleaseHelp
  • 131
  • 1
  • 10
  • *I'm trying to remove the duplicate combinations from my cross join select statement* There is no duplicates in shown output, each final row is unique. – Akina Nov 13 '20 at 05:12
  • *The result that I want* Why you store `(1,2)` and remove (1,4) and (1,6)? Define the criteria... maybe you need simpy add `GROUP BY credit_id` and wrap `loan_id` expression into `MIN()` function? – Akina Nov 13 '20 at 05:13
  • doing `GROUP BY credit_id` and wrap `loan_id` expression into `MIN()`. Making it showing `credit id` 1,1,1 and `loan_id` 2,4,6 – ImBaldingPleaseHelp Nov 13 '20 at 06:53

1 Answers1

0
SELECT TRIM(REPLACE(REPLACE(city, 'Credit', ''), 'Loan', '')) city,
       GROUP_CONCAT(CASE WHEN LOCATE('Credit', city) THEN id END) credit_ids,
       SUM(CASE WHEN LOCATE('Credit', city) THEN fishing_segment END) credit_fishing_segment,
       GROUP_CONCAT(CASE WHEN LOCATE('Loan', city) THEN id END) loan_ids,
       SUM(CASE WHEN LOCATE('Loan', city) THEN fishing_segment END) loan_fishing_segment
FROM data
GROUP BY 1;

fiddle

Akina
  • 39,301
  • 5
  • 14
  • 25