Mufleeh Posted July 18, 2011 Share Posted July 18, 2011 Hi, I have been using below 2 queries separately and they are working fine. Queries are from views, now I want them to be joined and display the data in one single table. I couldn't make it in the way I tried, can anyone please let me know a better way? $query = $this->dbh->prepare("SELECT * FROM view_teachers_info WHERE nic IN (SELECT nic FROM view_teachers_current_school WHERE school_id = '$this->schoolId')"); $query = $this->dbh->prepare("SELECT * FROM view_teachers_first_school WHERE nic IN (SELECT nic FROM view_teachers_current_school WHERE school_id = '$this->schoolId')"); What I tried was simply, $query = $this->dbh->prepare("SELECT * FROM view_teachers_info,view_teachers_first_school WHERE nic IN (SELECT nic FROM view_teachers_current_school WHERE school_id = '$this->schoolId')"); Regards, Mufleeh Quote Link to comment https://forums.phpfreaks.com/topic/242248-how-to-join-2-queries/ Share on other sites More sharing options...
teynon Posted July 18, 2011 Share Posted July 18, 2011 SELECT t1.fieldName as OutputFieldName, t2.fieldName as OutputFieldName2 FROM view_teachers_info as t1 INNER JOIN view_teachers_first_school as t2 WHERE t1.school_id = '{$this->schoolId}' AND t1.school_id = t2.school_id Make sure you change the "t1.fieldName as OutputFieldName, t2.fieldName as OutputFieldName2" part to match your database. Quote Link to comment https://forums.phpfreaks.com/topic/242248-how-to-join-2-queries/#findComment-1244105 Share on other sites More sharing options...
Psycho Posted July 18, 2011 Share Posted July 18, 2011 NOTE: Moving post to the MySQL forum Doing an INNER JOIN will result in duplicates in the result set. I think the solution here is to simply use a UNION to get those queries returned as a single result set. BUT - you must ensure you are returning the same number of fields from both tables AND that the corresponding field types are "similar". I.e. if the third field from the first table is a Date type, then the third field of the second table cannot be an int. But, variations in numeric/text fields should be fine. So, if those tables do not have all the same number and type of fields (in the same order), then you need to specify the fields you need from the two tables such that those criteria are met $sql = "SELECT schoolname, startdate, grade FROM view_teachers_info WHERE nic IN (SELECT nic FROM view_teachers_current_school WHERE school_id = '$this->schoolId') UNION SELECT schoolname, startdate, grade FROM view_teachers_first_school WHERE nic IN (SELECT nic FROM view_teachers_current_school WHERE school_id = '$this->schoolId')" $query = $this->dbh->prepare($sql); Quote Link to comment https://forums.phpfreaks.com/topic/242248-how-to-join-2-queries/#findComment-1244176 Share on other sites More sharing options...
Mufleeh Posted July 19, 2011 Author Share Posted July 19, 2011 Hi all, Thanks for the responses, I need different fields from those two views. In example I need the schoolname and grade from view_teachers_info and startdate from view_teachers_first_school. This seems to be not possible in the way you've suggested. Please advice. Regards, Mufleeh Quote Link to comment https://forums.phpfreaks.com/topic/242248-how-to-join-2-queries/#findComment-1244408 Share on other sites More sharing options...
Psycho Posted July 19, 2011 Share Posted July 19, 2011 Thanks for the responses, I need different fields from those two views. In example I need the schoolname and grade from view_teachers_info and startdate from view_teachers_first_school. This seems to be not possible in the way you've suggested. Please advice. Without knowing more specifics about the table structure and organization it is difficult to advise further. If the two result sets have no real relationship why do you need to get the results in a single query? What is wrong with what you have? If the records from one table are a one-to-one relationship with a teacher and the other table has a one-to-one or many-to-one relationship we can probably get everything in one query. Quote Link to comment https://forums.phpfreaks.com/topic/242248-how-to-join-2-queries/#findComment-1244434 Share on other sites More sharing options...
Mufleeh Posted July 19, 2011 Author Share Posted July 19, 2011 Hi, Every views are linked with a field 'nic' and different fields from different views have to be retrieved. I tried doing that linking the keys in different views. But it seems like there is an issue. $query = $this->dbh->prepare("SELECT * FROM view_teachers_info, view_teachers_first_school, view_teachers_current_school WHERE view_teachers_info.nic IN (SELECT nic FROM view_teachers_current_school WHERE school_id = '$this->schoolId') AND view_teachers_current_school.nic = view_teachers_first_school.nic AND view_teachers_first_school.nic = view_teachers_information.nic "); Quote Link to comment https://forums.phpfreaks.com/topic/242248-how-to-join-2-queries/#findComment-1244436 Share on other sites More sharing options...
fenway Posted July 21, 2011 Share Posted July 21, 2011 What "issue"? Quote Link to comment https://forums.phpfreaks.com/topic/242248-how-to-join-2-queries/#findComment-1245552 Share on other sites More sharing options...
Psycho Posted July 21, 2011 Share Posted July 21, 2011 If you want help you need to take a moment and try to explain what you are trying to achieve in a meaningful manner. Give an explanation of the data stored in each table and what you are trying to get out of the database. I can make some assumptions based on the table names, but I'm not going to invest any time in trying to provide a query when its more likely than not that I have made the wrong assumption. Looking at the first two queries you provided, I can see the relationship between the two tables and the "view_teachers_current_school" table. But, without knowing how those two other tables relate to each other I can't really know what you need. Here are my assumptions/question, please either confirm or correct and provide additional information if needed: - I assume that "nic" is a unique ID for each teacher. - Is there only ONE record per teacher (or nic) in the "view_teachers_info" table or can there be multiple? - Is there only ONE record per teacher (or nic) in the "view_teachers_first_school" table or can there be multiple? - Is it possible for a record to exist in one of those tables associated with a "nic" but no record(s) in the other table for the same "nic"? If so, is there one table that will have at least one associated record for each "nic" or is there one table that should be considered the "primary"? That means if there is a record in the primary, but no associated record in the secondary, it should be included in the results. But, if an associated record exists in the secondary, but not the primary, it will not display in the results. Quote Link to comment https://forums.phpfreaks.com/topic/242248-how-to-join-2-queries/#findComment-1245731 Share on other sites More sharing options...
Mufleeh Posted July 21, 2011 Author Share Posted July 21, 2011 Hi, Firstly I'm really sorry for not submitting my question properly. Please refer below and check whether its possible to help me out. - I assume that "nic" is a unique ID for each teacher. - Yes it is - Is there only ONE record per teacher (or nic) in the "view_teachers_info" table or can there be multiple? - Only one record per teacher - Is there only ONE record per teacher (or nic) in the "view_teachers_first_school" table or can there be multiple? - Only one record per teacher - Is it possible for a record to exist in one of those tables associated with a "nic" but no record(s) in the other table for the same "nic"? If so, is there one table that will have at least one associated record for each "nic" or is there one table that should be considered the "primary"? That means if there is a record in the primary, but no associated record in the secondary, it should be included in the results. But, if an associated record exists in the secondary, but not the primary, it will not display in the results. All the records (teachers/nic) in view_teachers_info also in view_teachers_first_school. We can consider view_teachers_info as the primary table. Quote Link to comment https://forums.phpfreaks.com/topic/242248-how-to-join-2-queries/#findComment-1245768 Share on other sites More sharing options...
Psycho Posted July 21, 2011 Share Posted July 21, 2011 Hmm, I question if you have the right database design. Instead of having separate tables for current school,, first school (and I'm assuming other previous schools) it would mmke more sense to have a single table with an identifier to determine first/current school. No matter, I *think* this query will get you what you need. If not, please state what - EXACTLY - is wrong with the results. SELECT *.view_teachers_info, *.view_teachers_first_school FROM view_teachers_current_school JOIN view_teachers_info USING nic JOIN view_teachers_first_school USING nic WHERE view_teachers_current_school.nic = '$this->schoolId' Quote Link to comment https://forums.phpfreaks.com/topic/242248-how-to-join-2-queries/#findComment-1245838 Share on other sites More sharing options...
Mufleeh Posted July 22, 2011 Author Share Posted July 22, 2011 Hi, I am receiving below error message. PDOStatement::errorInfo(): Array ( [0] => 00000 [1] => 7 [2] => ERROR: syntax error at or near "." LINE 1: SELECT * .view_teachers_information, * .view_teachers_first_... ^ ) Regards, Mufleeh Quote Link to comment https://forums.phpfreaks.com/topic/242248-how-to-join-2-queries/#findComment-1246063 Share on other sites More sharing options...
Psycho Posted July 22, 2011 Share Posted July 22, 2011 ERROR: syntax error at or near "." LINE 1: SELECT * .view_teachers_information, * .view_teachers_first_... ^ ) You have a space between "*" and the ".tablenames". Why did you deviate from what I supplied? Quote Link to comment https://forums.phpfreaks.com/topic/242248-how-to-join-2-queries/#findComment-1246200 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.