SQL QUERY - JOIN

[MikeLee]

AS I know, MYSQL don't support full outer join
so i have some problem for my query,
I have 2 table
table 1
{ id
date
other infomation....
}

table 2
{ id
date
remark
}
now i need to show a table something like other join,
show the record once it exist on each table or both,
how can i do this?

THANKS~

 

[GiffordS]

I don't really understand the question as it's phrased. I understand the table structures, but I don't know what you mean when you say you want to "show a table something like other join, show the record once it exist on each table or both". Are you wanting to create a third table? Do you want to do a query of only records that exist in both tables?

 

[MikeLee]

thanks for your reply~
let say table 1 have record

staff_id date other field
1 2002-08-10 ....
1 2002-08-13 ....

table 2 have record
staff_id date remark
1 2002-08-10 remark1
1 2002-08-12 remark2

i would like to have result like
staff_id date remark other field
1 2002-08-10 remark1 .....
1 2002-08-12 remark2 x
1 2002-08-13 x .....

 

[cwong1]

You can use two queries (LEFT JOIN) in order to get the information you need.

SELECT t1.staff_id, t1.date, t2.remark, t1.other_field FROM
table1 AS t1 LEFT JOIN table2 AS t2 ON t1.date=t2.date ;

SELECT t1.staff_id, t2.date, t2.remark, t1.other_field FROM table2 AS t2 LEFT JOIN table1 AS t1 ON t1.date=t2.date WHERE t1.date IS NULL ;

 

[GiffordS]

I'm not aware of a MySql join that will accomplish this. Maybe someone else will. I certainly hope so, because the only other solution I can think of is to change the field name of "Date" in table 2 and do the join with a simple conditional statement when you fetch the results. Say the "Date" field in table2 was changed to "Dateb". Then, the query is very simple. Let's assume you want to drop the results into a table for display.

echo &quot;<table>&quot;;

select * from table1, table2
where table1.staff_id = table2.staff_id

The trick is to account for all five fields in the process of stepping through the results array.

For instance if the query were $q, and the run statement were $r, then you would step through the array:

While($w = mysql_fetch_array($r, MYSQL_ASSOC))
{
$s = $w[&quot;staff_id&quot;];
$d = $w[&quot;Date&quot;];
$db = $w[&quot;Dateb&quot;];
$m = $w[&quot;remark&quot;];
$o = $w[&quot;other_field&quot;];

if($d == $db)
{
echo &quot;<tr><td>&quot;.$s.&quot;</td><td>&quot;.$d.&quot;</td><td>&quot;.$m.&quot;</td><td>&quot;.$o.&quot;</td></tr>&quot;;
}
else
{
echo &quot;<tr><td>&quot;.$s.&quot;</td><td>&quot;.$d.&quot;</td><td></td><td>&quot;.$o.&quot;</td></tr>&quot;;
echo &quot;<tr><td>&quot;.$s.&quot;</td><td>&quot;.$db.&quot;</td><td>&quot;.$m.&quot;</td><td></td></tr>&quot;;
}}
echo&quot;</table>&quot;;

The table layout is crude, but should be effective. Basically all you are doing is taking each row and saying &quot;if the date from table1 is the same as the date from table2, then echo them both as one result. If not, echo the date from table1 with the other field while leaving the remark field blank, then, on a separate line echo the date from table2 with the remark while leaving the other field blank&quot;. I tested this and it does return the results you desire. The only thing is that you would have to change the name of the &quot;Date&quot; field on one of the tables. Otherwise MySql will replace the initial table1 Date value with the table2 Date value.

 

[MikeLee]

yes, i also think of this solution,
but i would also like to make it in one table which is order by date,

i try to use union, but fail..

 

[GiffordS]

MySql does not support Union.

 

[MikeLee]

Oh, i know,
MySQL does have Union after MySQL v4.0

but i am using MySQL 3.23.49,

 

[GiffordS]

To sort by date accurately you could just dump the results into a two-dimensional array rather than dumping them straight to a table using the same basic construction conditional statement. Then, you could sort the array by the date key and dump those results into a table. Seems a bit convoluted, though. There must be a better way.