Combining two tables together twice and then pivot in MySQL-CodePudding

I have been trying to solve this for a while, and I am stumped.

I have two tables, and I am basically trying to combine them and pivot them. I'm doing it in MySQL.

Here are the tables:

pogo_dex:

ID	DexNum	Pokemon	Candy
1	1	Bulbasaur	Bulbasaur
2	2	Ivysaur	Bulbasaur
3	3	Venusaur	Bulbasaur
4	4	Charmander	Charmander
5	5	Charmeleon	Charmander
6	6	Charizard	Charmander
7	7	Squirtle	Squirtle
8	8	Wartortle	Squirtle
9	9	Blastoise	Squirtle
10	10	Caterpie	Caterpie
11	11	Metapod	Caterpie

pogo_meta:

ID	DexNum	Name	Form	Shadow	League	Tier
50	2	Ivysaur			Great	3
52	2	Ivysaur		Shadow	Great	3
993	3	Venusaur			Attackers	7
822	3	Venusaur			Master	4
456	3	Venusaur		Shadow	Ultra	3
11	3	Venusaur		Shadow	Great	2
443	3	Venusaur			Ultra	3
804	3	Venusaur		Shadow	Master	4
920	3	Venusaur	Mega		Attackers	1
939	3	Venusaur		Shadow	Attackers	3
3	3	Venusaur			Great	2
964	6	Charizard		Shadow	Attackers	4
426	6	Charizard		Shadow	Ultra	2
427	6	Charizard			Ultra	2
806	6	Charizard		Shadow	Master	4
941	6	Charizard		Mega X	Attackers	3
907	6	Charizard		Mega Y	Attackers	0.9
97	6	Charizard		Shadow	Great	3
815	6	Charizard			Master	4
127	6	Charizard			Great	3
1004	6	Charizard			Attackers	7
823	9	Blastoise			Master	5
919	9	Blastoise	Mega		Attackers	1
104	9	Blastoise			Great	3
429	9	Blastoise			Ultra	2
328	12	Butterfree			Great	5
968	15	Beedrill	Mega		Attackers	4
105	15	Beedrill			Great	3
45	18	Pidgeot			Great	2
441	18	Pidgeot			Ultra	2

In the end, this is what I want to get out of them:

ID	DexNum	Pokemon	Candy	Attackers	Master	Ultra	Great
1	1	Bulbasaur	Bulbasaur	1	4	3	2
2	2	Ivysaur	Bulbasaur	1	4	3	2
3	3	Venusaur	Bulbasaur	1	4	3	2
4	4	Charmander	Charmander	0.9	4	2	3
5	5	Charmeleon	Charmander	0.9	4	2	3
6	6	Charizard	Charmander	0.9	4	2	3
7	7	Squirtle	Squirtle	1	5	2	3
8	8	Wartortle	Squirtle	1	5	2	3
9	9	Blastoise	Squirtle	1	5	2	3
10	10	Caterpie	Caterpie	0	0	0	5
11	11	Metapod	Caterpie	0	0	0	5

I know I have to left join the pogo_dex to the pogo_tiers to get the Candy associated with the Tiers. So, I wrote this:

SELECT b.Candy, a.League, Min(a.Tier) as Tier, a.Shadow, a.Form from `pogo_meta` a left join `pogo_dex` b on a.DexNum=b.DexNum GROUP BY b.Candy, a.League, a.Shadow, a.Form ORDER BY CAST(a.DexNum As DECIMAL) ASC

Then, I joined that to the rest like this:

SELECT c.DexNum, c.Pokemon, d.Shadow, d.Form, d.League, d.Tier FROM `pogo_dex` c LEFT JOIN (SELECT b.Candy, a.League, Min(a.Tier) as Tier, a.Shadow, a.Form from `pogo_meta` a left join `pogo_dex` b on a.DexNum=b.DexNum GROUP BY b.Candy, a.League, a.Shadow, a.Form ORDER BY CAST(a.DexNum As DECIMAL) ASC) d on c.Candy=d.Candy;

But now I'm stuck. How do I pivot it? I want to see the Min of each Tier for each Candy type.

CodePudding user response：

I tried to solve it; but result_set doesn't match. I share my code and result_set here. MySQL has no pivot function; but you can use case statement to achieve a similar result.

SELECT c.ID, c.DexNum, c.Pokemon, c.Candy,
    MIN(case when d.League = 'Attackers' then d.Tier end) as Attackers,
    MIN(case when d.League = 'Master' then d.Tier end) as Master,
    MIN(case when d.League = 'Ultra' then d.Tier end) as Ultra,
    MIN(case when d.League = 'Great' then d.Tier end) as Great
FROM pogo_dex c
LEFT JOIN
(SELECT b.Candy, a.Tier, a.Shadow, a.League, a.Form from pogo_tiers a 
left join pogo_dex b on a.DexNum=b.DexNum
GROUP BY a.DexNum, a.Tier, b.Candy, a.League, a.Shadow, a.Form
ORDER BY a.DexNum, b.Candy, a.League, a.Form 
) d 
    ON c.Candy=d.Candy
GROUP BY c.ID, c.DexNum, c.Pokemon, c.Candy;

Resulting query looks like this: I hope It helps somehow.

CodePudding user response：

use alises, your table1 AS a, your table2 AS b

there's lack of alises initialization do it.