0

I'm trying to cross join 2 select statements but they return duplicate output. I have tried the solutions here but nothing seemed to be working. I'm kind of confused right now. Is there any other way to remove the duplicate pair?

THE DATA

table = vitamin
+----------------+-------+
|  vitamin_name  | price |  
+----------------+-------+
| pill 1         |    10 |  
| extreme pill 1 |    20 |  
| pill 2         |    30 |  
| extreme pill 2 |    40 |  
+----------------+-------+

CODE OUTPUT

+----------------+----------+---------------+---------------+
|  extreme_name  | reg_name | price extreme | price regular |
+----------------+----------+---------------+---------------+
| extreme pill 1 | pill 1   |            20 |            10 |
| extreme pill 2 | pill 1   |            40 |            10 |
| extreme pill 2 | pill 2   |            40 |            30 |
+----------------+----------+---------------+---------------+

DESIRED OUTPUT

+----------------+----------+---------------+---------------+
|  extreme_name  | reg_name | price extreme | price regular |
+----------------+----------+---------------+---------------+
| extreme pill 1 | pill 1   |            20 |            10 |
| extreme pill 2 | pill 2   |            40 |            30 |
+----------------+----------+---------------+---------------+

MY CODE

BEGIN
SET @rownum := 0;
SET @rownum2 := 0;

SELECT DISTINCT t3.name as `extreme_name`,t3_reg.name as `reg_name`, 
t3.extreme as `price extreme`,
t3_reg.regular as `price regular`
FROM

(SELECT t2.rn as `rownum`,t2.name as `name`,t2.extreme as `extreme` FROM
    (SELECT  CASE WHEN t.rownum%2 = 0 THEN t.name END AS `name`,
        CASE WHEN t.rownum%2 = 0 THEN t.rownum END AS `rn`,
        CASE WHEN t.rownum%2 = 0 THEN t.extreme END AS `extreme` 
        FROM
        (SELECT vitamin_name as `name`,(@rownum:=@rownum+1) as  `rownum`,price AS `extreme` FROM vitamin) as t
    )as t2
        WHERE t2.rn IS NOT NULL) as t3

CROSS JOIN

(SELECT t2.rn as `rownum`,t2.name as `name`,t2.regular as `regular` FROM
    (SELECT  CASE WHEN t.rownum%2 <> 0 THEN t.name END AS `name`,
        CASE WHEN t.rownum%2 <> 0 THEN t.rownum END AS `rn`,
        CASE WHEN t.rownum%2 <> 0 THEN t.regular END AS `regular` 
        FROM
        (SELECT vitamin_name as `name`,(@rownum2:=@rownum2+1) as `rownum`,price AS `regular` FROM vitamin) as t
    )as t2
        WHERE t2.rn IS NOT NULL) as t3_reg
        WHERE t3_reg.rownum <= t3.rownum;
        
END

EDITED Here's the FIDDLE

0 Answers0