Trying to perform a select that sometimes involves an empty table

[KhornetheGrim]

First, I'm using MySQL Ver 5.1.33

 

Here is my problem. I am making a database for D&D monsters. One particular table, subtypes, does not have a value for every monster. Or rather, not every monster has a subtype. When I perform a select to try to list all of the monsters in the database (to be used later with PHP to list them all in a web page) it only lists the monsters with a subtype.

 

Is there any way to perform a select that will grab all of the monsters, including all of the ones with no subtype?

 

Following is a list of the tables I'm using and a copy of the select statement I am using. Sorry, but I am not sure how to tack it into a code window.

 

+------------------------+

| Tables_in_monster_list |

+------------------------+

| alignments            |

| crs                    |

| environ_associations  |

| environments          |

| monsters              |

| organization          |

| sizes                  |

| subtype_associations  |

| subtypes              |

| types                  |

+------------------------+

 

A little explanation of the tables. The monsters <=> subtypes relationship is many to many, so I've created the subtype_associations table as an intermediary. It's the subtype_associations table that doesn't have any values for the monsters without subtypes, which is causing those monsters to be excluded from the select results.

 

SELECT name AS Name, cr AS CR, size AS Size, types AS Type, subtype AS Subtype, environ AS Environment, book_page AS Book, align AS Alignment, org AS Organization

FROM monsters, crs, sizes, types, subtypes, subtype_associations, environments, environ_associations, alignments, organization

WHERE monsters.cr_id = crs.cr_id

AND monsters.size_id = sizes.size_id

AND monsters.type_id = types.type_id

AND monsters.monster_id = subtype_associations.monster_id

AND subtypes.subtype_id = subtype_associations.subtype_id

AND monsters.monster_id = environ_associations.monster_id

AND environments.environ_id = environ_associations.environ_id

AND monsters.align_id = alignments.align_id

AND monsters.org_id = organization.org_id;

 

Thanks for any help that can be offered.

[fenway]

Yikes... hard to follow that... I'd re-write in proper ansi-join format (using INNER JOIN and ON clauses).

[KhornetheGrim]

Sorry, I'm teaching myself MySQL so my code isn't always the greatest. Hopefully this rewrite helps, and I think I've actually solved my problem, but now I'm getting an error that makes no sense to me.

 

Here is my new select;

 

SELECT monsters.name AS Name, crs.cr AS CR, sizes.size AS Size, types.types AS Type, subtypes.subtype AS Subtype, environments.environ AS Environment, monsters.book_page AS Book, alignments.align AS Alignment, organization.org AS Organization

FROM monsters, subtype_associations, environ_associations

INNER JOIN crs ON monsters.cr_id = crs.cr_id

INNER JOIN Size ON monsters.size_id = sizes.size_id

INNER JOIN Type ON monsters.type_id = types.type_id

LEFT JOIN Subtype ON monsters.monster_id = subtype_associations.monster_id AND subtypes.subtype_id = subtype_associations.subtype_id

LEFT JOIN Environment ON monsters.monster_id = environ_associations.monster_id AND environments.environ_id = environ_associations.environ_id

INNER JOIN Alignment ON monsters.align_id = alignments.align_id

INNER JOIN Organization ON monsters.org_id = organization.org_id;

 

And here is the exact error I'm now getting;

 

ERROR 1054 (42S22): Unknown column 'monsters.cr_id' in 'on clause'

 

I have no idea why this is, because when I use;

 

SELECT monsters.name AS Name, crs.cr AS CR

FROM monsters

INNER JOIN crs ON monsters.cr_id = crs.cr_id;

 

I get results.

 

I'm sure my code is probably awful again, and I apologize for that, but hopefully someone is able to help me. I've been doing incessant searches, trying to find an answer, but have come up with nothing.

[KhornetheGrim]

Basically, I am trying to include a table in my SELECT that sometimes does not contain any data for a particular monster. I want to list all of the monsters, including the ones with no data in that one table, but so far I have only been able to list the monsters that have data in that specific table.

[fenway]

That's because you're mixing comma and JOIN operators -- and now you're using mysql 5.  The precedence of the operators has changed.

 

Easiest way to hack this for now is to wrap all of the tables "joined" with a comma in parentheses; long term, you should properly rewrite the query..

[KhornetheGrim]

YES! I finally figured it out! Again, my code probably sucks, but it works!

 

This is the SELECT that finally did it for me;

 

SELECT monsters.name AS Name, crs.cr AS CR, sizes.size AS Size, types.types AS Types, subtypes.subtype AS Subtype, environments.environ AS Environment, monsters.book_page AS Book, alignments.align AS Alignment, organization.org AS Organization

FROM monsters

INNER JOIN crs ON monsters.cr_id = crs.cr_id

INNER JOIN sizes ON monsters.size_id = sizes.size_id

INNER JOIN types ON monsters.type_id = types.type_id

LEFT JOIN (subtypes

    LEFT JOIN subtype_associations

        ON subtypes.subtype_id = subtype_associations.subtype_id)

    ON monsters.monster_id = subtype_associations.monster_id

LEFT JOIN (environments

    LEFT JOIN environ_associations

        ON environments.environ_id = environ_associations.environ_id)

    ON monsters.monster_id = environ_associations.monster_id

INNER JOIN alignments ON monsters.align_id = alignments.align_id

INNER JOIN organization ON monsters.org_id = organization.org_id;

 

Just in case anyone cares about the solution I found.