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