MySQL Complex Joins not working

[carpga]

I am in the process of migrating a database from a previous application developer for a non-profit. (this is all volunteer time) In the process of doing this, I have run into a very complex query which has been requested, and am having a serious issue getting the joins to work, in addition to attempting to figure out how to perform a large number of joins in a single query. The current code is in the works...but looks like this within the PHP:

 

"SELECT ID, AnimalName, ShelterCode, ShelterLocation, SpeciesID, BreedID, Sex, DateOfBirth, (SELECT BaseColour FROM basecolour WHERE ID=BaseColourID) AS Color, IdentichipNumber, DateBroughtIn  FROM animal;"

 

Certain columns within animal are only a key with a reference to a primary key in an alternative table. For example, "SpeciesID" within the animal table correlates to "ID" in the "Species" table. As this query exists, I get results. However, when I attempt anything along the lines of SpeciesID.animal, the query returns no results. The BaseColour section presents the correct results as written, however I can only seem to get this section to work in a single column. This is rather frustrating, because it works in PostGRE, however after transitioning the information over, it seems to be giving persistent issues with the joins. Previously I was using MySQL 4.x and am now on 5.1.x which I have also read may be causing some of the issues (not because of MySQL, but because of the way I am performing the SELECT statement). Of the fields shown, I am looking to join ShelterLocation, SpeciesID, BreedID, Sex, and BaseColour to different tables which once completed, will not return a reference ID, but rather readable text (so currently I get a result for BreedID as something like "45" and with the join would see something like "Boxer").

 

If anyone needs, I can actually provide the full code being executed from end-to-end as this is in development on my local system and not publicly accessible.

[mikosiko]

this have a very short answer....  JOIN 

 

manual: http://dev.mysql.com/doc/refman/5.0/en/join.html

 

example: http://www.tizag.com/mysqlTutorial/mysqljoins.php