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. Quote 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 Quote 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
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.