Max Snow Max Snow - 1 month ago 16
MySQL Question

GROUP BY and SELECT in a SELECT

My problem is solved but now i have a few performance questions.

I want an overview of my caught pokemon for all editions, it works as far as i tried, but i don't think it's best practice.

select
#p.idpokemons as ID,
#p.name as Pokemon,
#t.name as Trainer,
tpe.idpokemons as ID,
(select name from pokemons p where p.idpokemons = tpe.idpokemons) as Pokemon,
(select name from trainer t where t.idtrainer = tpe.idtrainer) as Trainer,
max(case when ideditions = 'x' then status end) as statusX,
max(case when ideditions = 'y' then status end) as statusY,
max(case when ideditions = 'or' then status end) as statusOR,
max(case when ideditions = 'as' then status end) as statusAS,
max(case when ideditions = 'sun' then status end) as statusSun,
max(case when ideditions = 'moon' then status end) as statusMoon
from trainer_pokemon_edition tpe
#JOIN pokemons p ON p.idpokemons = tpe.idpokemons
#JOIN trainer t ON t.idtrainer = tpe.idtrainer
group by tpe.idpokemons;


Edit:
Just changed the Query to something like Thorstens answer, because its faster then my old one.
I commented the joins out, because in my application im getting 721 rows returned and with joins the query is slower than with subquery
0,021s/0,00034s
JOINs against
0,0030s/0,013s
Subquery Duration/Fetch.
Why is it like this, the fetchtime on Joins is faster, but the duration not?

image of db design

And here is a link to sqlfiddle, with testdata.

Answer

You want to show all your pokemons and their statuses? But their are many trainer_pokemon_edition records per pokemon, so there can be different statuses per x, per y etc. You'll have to decide which one to show; the maximum? a concatenated string showing all statuses? something else? It's also strange that there is a table holding the editions, but your query deals with certain ones and shows them in columns.

However, to get the status per pokemon simply aggregate your data:

select 
  (select name from pokemons p where p.idpokemons = tpe.idpokemons) as pokemon,
  max(case when ideditions = 'x' then status end) as statusx, 
  max(case when ideditions = 'y' then status end) as statusy, 
  max(case when ideditions = 'or' then status end) as statusor, 
  max(case when ideditions = 'as' then status end) as statusas, 
  max(case when ideditions = 'sun' then status end) as statussun, 
  max(case when ideditions = 'moon' then status end) as statusmoon, 
  max(case when ideditions = 'b' then status end) as statusb, 
  max(case when ideditions = 'r' then status end) as statusr 
from trainer_pokemon_edition tpe
group by idpokemons;

Of course you can also join with the pokemons table instead of getting the name in a subquery in the select clause.