carpga Posted March 23, 2011 Share Posted March 23, 2011 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. Link to comment https://forums.phpfreaks.com/topic/231452-mysql-complex-joins-not-working/ Share on other sites More sharing options...
mikosiko Posted March 23, 2011 Share Posted March 23, 2011 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 Link to comment https://forums.phpfreaks.com/topic/231452-mysql-complex-joins-not-working/#findComment-1191136 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.