1

Sorry for the strange title, but I'm not quite sure how to even title this question. It's a problem I've faced for a few days now, and I can't figure out how to solve it.

The problem I'm having deals with Veekun's pokemon database when displaying all types of one pokemon on one row in a table. Thus far, I've got the pokemon displaying correctly, but all pokemon with more than one type display in multiple rows. An example of this would be Bulbasaur, who is Grass/Poison, displaying in my table as both result 1 AND 2, each row for his specific type(I tried to post an image, but rep is too low)

I'd like for both types to show on one row in my table, but I'm not quite sure how to do so. I'm not sure if I can use a JOIN for this specific problem, being as both types are in one table.

Here's my current query for the table:

SELECT pokemon.id, pokemon_species_flavor_text.flavor_text, pokemon_species_names.genus,  pokemon_species_names.name, type_names.name
FROM pokemon, pokemon_species_flavor_text, pokemon_species_names, type_names, pokemon_types
WHERE pokemon.species_id = pokemon_species_flavor_text.species_id
AND pokemon.species_id = pokemon_species_names.pokemon_species_id
AND pokemon_types.pokemon_id = pokemon.id
AND pokemon_types.type_id = type_names.type_id
AND pokemon_species_flavor_text.version_id =24
AND pokemon.id <2000
AND pokemon_species_names.local_language_id =9
AND type_names.local_language_id =9

Here's a sample of what the table is showing if I just select the id, pokemon, and type:

__________________________
| id ' pokemon   ' type  
| 1  ' bulbasaur ' grass 
| 1  ' bulbasaur ' poison
| 2  ' ivysaur   ' grass 
| 2  ' ivysaur   ' poison
--------------------------

...and so on. I'd like for the types to show in one row, however.(sorry for the strange formatting, seems there's some sort of stackoverflow specific formatting i'm using or something)

Any help on the matter is much appreciated. I know my question may be sloppy, but it's much easier to explain if I'm able to use images. And sadly, I cannot use them yet.

EDIT: Thanks to everyone who answered, I solved my problem using GROUP_CONCAT, per the suggestions from a couple different people who answered. When I just did GROUP_CONCAT(type_names.name) it started spitting them out in random order, so I added a couple GROUP BYs into the statement. Here's the SQL I'm now using, which works:

SELECT pokemon.id, pokemon_species_flavor_text.flavor_text, pokemon_species_names.genus,     pokemon_species_names.name, GROUP_CONCAT( type_names.name ) 
FROM pokemon, pokemon_species_flavor_text, pokemon_species_names, type_names, pokemon_types
WHERE pokemon.species_id = pokemon_species_flavor_text.species_id
AND pokemon.species_id = pokemon_species_names.pokemon_species_id
AND pokemon_types.pokemon_id = pokemon.id
AND pokemon_types.type_id = type_names.type_id
AND pokemon_species_flavor_text.version_id =24
AND pokemon.id <2000
AND pokemon_species_names.local_language_id =9
AND type_names.local_language_id =9
GROUP BY pokemon.id, pokemon_species_names.name

Once again, thanks to all that answered!

KruSuPhy
  • 43
  • 7
  • 1
    It all depends on what RDBMS type the database is. – cha Mar 13 '14 at 22:42
  • If it's MySQL, take a look at GROUP_CONCAT. – Barmar Mar 13 '14 at 22:44
  • If you're using an older or less function-rich RDMBS however, such as Microsoft SQL Server, you will have to concatenate the strings together using an aggregate function. Basically your select code will have a sub-select into a fake column name (like "types") that selects the type for the outer row id (repeatedly when needed) and concatenates the type comma delimited into the result set. There are sometimes certain tricks you need like "xml path()", again depending on the RDBMS used. With further information one of us can probably help you with more specifics. – Don Kelley Mar 13 '14 at 22:49

0 Answers0