How to join 2 queries?

[Mufleeh]

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

[teynon]

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.

[Psycho]

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);

[Mufleeh]

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

[Psycho]

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.

[Mufleeh]

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

");

[fenway]

What "issue"?

[Psycho]

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.

[Mufleeh]

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.

[Psycho]

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'

[Mufleeh]

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

[Psycho]

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?