CODEWITHSUNDEEP
mysql
paradoxparabola
paradoxparabola

Reputation: 73

MySQL - Only Select certain rows depending on different conditions

So, I've already asked a questoin similar to this here but I'll ask again since what I need is similar to that but with a different conditions.

As an example I have a Pokemon column with certain data inside, many of them repeat

Pokemon

Mega Blastiose
Blastoise
Mega Charizard
Mega Venusaur
Pichu
Pikachu
Alakazam
Pichu
Kadabra
Squirtle
Wartotle
Wartotle
Diglett

What I need is to select only certain data, if a specific data on the same column exists. Certain data from the pokemon column must exists before I fetch data via SELECT

What I want is something is a query that do something like this but on multiple data. 

SELECT * FROM table 
(
    If Pichu doesn't exists then don't SELECT Pikachu
    If (Abra OR Kadabra) doesn't exists then don't SELECT Kadabra
    If (Squirtle OR Wartotle OR Blastoise) doesn't exists then don't SELECT Mega Blastoise
    If Pidgety doesn't exists then don't SELECT Pidgeotto
    If (Charmarder OR Charmeleon OR Charizard) doesn't exists then don't SELECT Mega Charizard
    If (Bulbasaur OR Ivysaur OR Venusaur) doesn't exists then don't SELECT Mega Charizard
)

So the final result of the SELECT query I need would result in something in this.

Mega Blastiose
Blastoise
Pichu
Pikachu
Alakazam
Pichu
Kadabra
Squirtle
Wartotle
Wartotle
Diglett

It ignored, Mega Charizard & Mega Venusaur since they are the only data that didn't meet their condition while fetching everything else. I know it's a bit confusing request but it's the query I'm looking for

Upvotes: 0

Views: 40

Answers (1)

Akina
Akina

Reputation: 42632

Directly:

SELECT pokemons.pokemon
FROM pokemons
LEFT JOIN
( SELECT CASE WHEN SUM(pokemon IN ('Pichu')) = 0 
              THEN 'Pikachu' END pokemon FROM pokemons -- false
  UNION
  SELECT CASE WHEN SUM(pokemon IN ('Abra', 'Kadabra')) = 0 
              THEN 'Kadabra' END FROM pokemons -- false
  UNION
  SELECT CASE WHEN SUM(pokemon IN ('Squirtle', 'Wartotle', 'Blastoise')) = 0 
              THEN 'Mega Blastoise' END FROM pokemons -- false
  UNION
  SELECT CASE WHEN SUM(pokemon IN ('Pidgety')) = 0 
              THEN 'Pidgeotto' END FROM pokemons -- true, but absent
  UNION
  SELECT CASE WHEN SUM(pokemon IN ('Charmarder', 'Charmeleon', 'Charizard')) = 0 
              THEN 'Mega Charizard' END FROM pokemons -- true
  UNION
  SELECT CASE WHEN SUM(pokemon IN ('Bulbasaur', 'Ivysaur', 'Venusaur')) = 0 
              THEN 'Mega Charizard' END FROM pokemons -- true, again
) exclude ON pokemons.pokemon = exclude.pokemon
WHERE exclude.pokemon IS NULL

fiddle

Upvotes: 1

Related Questions