Join 2 "mysql_query" results

[paengski13]

Hi all!

 

Is it possible to join the 2 mysql_query results before doing the mysql_fetch_assoc.

 

Here is the scenario.

 

Table: School_A_Tbl

student_id          student_name              date_enrolled

A00                          Anna                              March 20

A01                          Ben                                March 22

A02                          Carla                              March 24

 

 

Table: School_B_Tbl

student_id          student_name              date_enrolled

B00                          Dan                              March 21

B01                          Erl                                March 23

B02                          Fox                              March 25

 

Since there is no relationship with the 2 tables I want to combine them order by the date_enrolled

 

$resultA = mysql_query("SELECT * FROM School_A_Tbl");

$resultB = mysql_query("SELECT * FROM School_B_Tbl");

 

What I want to do is combine this data according to date_enrolled. It should be something like this.

 

student_id          student_name              date_enrolled

A00                          Anna                            March 20

B00                          Dan                              March 21

A01                          Ben                              March 22

B01                          Erl                                March 23

A02                          Carla                            March 24

B02                          Fox                              March 25

 

It would be a better help if $resultA and $resultB will be merged before I iterate it using mysql_fetch_assoc.

 

Thanks in advance.

[fugix]

you will most likely want to use a join here. for the correct syntax and examples you can visit here

[sunfighter]

Don't know if its asc or desc for the oredr but --

 

SELECT * FROM School_A_Tbl UNION SELECT * FROM School_B_Tbl ORDER BY date_enrolled DESC | ASC   

[fugix]

union might be a better choice here, however you will need to add ALL in your clause, as to not delete duplicate rows.

SELECT * FROM School_A_Tbl UNION ALL SELECT * FROM School_B_Tbl 

for more information on the UNION syntax/usage...visit here

 

Edit: Also, if you are going to use an ORDER BY clause to ORDER the entire UNION result, you will need to parenthesis the individual select statements and place the ORDER BY or LIMIT statements after the last one...e.g

(SELECT * FROM School_A_Tbl) 
UNION ALL 
(SELECT * FROM School_B_Tbl)
ORDER BY col_name DESC|ASC

[paengski13]

Thanks a lot! 

I guess UNION is the best way to solve this problem.

[fugix]

glad we could help, please mark this as solved, lower left hand of page

[paengski13]

Just 1 more question, is it possible to used UNION with different number of columns.

 

I got this error stating "[Err] 1222 - The used SELECT statements have a different number of columns"

 

Is there any other option for this?

[paengski13]

Just 1 more question, is it possible to used UNION with different number of columns.

 

I got this error stating "[Err] 1222 - The used SELECT statements have a different number of columns"

 

Is there any other option for this?

 

Ops I think I already found the answer, all I need to do is add NULL to the other table so that the number of columns will matched.

 

(SELECT column1, column2 FROM School_A_Tbl)

UNION ALL

(SELECT column1, NULL FROM School_B_Tbl)

 

Thanks again!