lalala lalala - 2 months ago 7
MySQL Question

Doing a three table join in MySQL

This is my table

program_join ('program_join_id, member_ID, program_schedule_id');

program_schedule (program_scedule_id, program_id, datetime');

program ('program_id, program_name);


This is my mysql

$mySql = "SELECT
program_join.program_join_id,
member.member_username,
program_join.program_schedule_id

FROM program_join

INNER JOIN member ON
program_join.member_ID=member.member_ID
ORDER BY program_join_id ASC LIMIT $hal, $line";


$myQry = mysql_query($mySql, $DB) or die ("wrong query : ".mysql_error());
$number = $hal;
while ($myData = mysql_fetch_row($myQry)) {
$number++;
$code = $myData[0];
?>
<tr align="center">
<td><?php echo $number;?></td>
<td><?php echo $myData[1];?></td>
<td><?php echo $myData[2];?></td>


result

No username program schedule
1 lalala 1
2 bababba 2


and under the 'program schedule', i want to show the program name that i have from the ' program_join.program_schedule_id'

please help me

Answer

Just use joins to get the program_name from the program table:

SELECT 
pj.program_join_id,
m.member_username, 
p.program_name

FROM program_join pj
INNER JOIN member m ON pj.member_ID=m.member_ID
INNER JOIN program_schedule ps ON ps.program_schedule_id = pj.program_schedule_id
INNER JOIN program p ON p.program_id = ps.program_id
ORDER BY ... blah blah