Join 3 tables

[raul66]

Hi everybody,
I want to join 3 tables and gain same data from these tables.
1.table:
usr - user information
usr_id
2.table:
res- results
res_usr_id
res_exc_id
res_result
3.table:
exc- excercises
exc_id
exc_excercise_name

Now I want print all results from one user and all excercises from one user.Can somebody help me?I don't know how to write this sql query...

Thanks

[Baldy76]

Hi everybody,
I want to join 3 tables and gain same data from these tables.
1.table:
usr - user information
usr_id
2.table:
res- results
res_usr_id
res_exc_id
res_result
3.table:
exc- excercises
exc_id
exc_excercise_name

Now I want print all results from one user and all excercises from one user.Can somebody help me?I don't know how to write this sql query...

Thanks

would you like the users that don't have exercises to also be displayed? If so just replace the word "inner" with "left".

<?php
$query = "select * from
		usr
		inner join res on usr.usr_id = res.res_usr_id
		inner join exc on res.res_exc_id = exc.exc_id";
?>

[djjjozsi]

if a user doesn't have results, that will be empty field in the results:
users , excercises , and res_result is the 3 table (you did not give these details)

<?php
$uid=1;
$sql="SELECT users.usr_id, users.usr, excercises.exc, excercises.exc_excercise_name, results.res, results.res_result
FROM excercises RIGHT JOIN (results RIGHT JOIN users ON results.res_usr_id = users.usr_id) ON excercises.exc_id = results.res_exc_id
WHERE (((users.usr_id)='$uid'))";
/* fetch here */
?>

[navi17]

this seems to very complex
here is the simple solution:

$sql="SELECT *
FROM   user as a, resutls as b,excercises as c  where  a.usr_id= b.res_usr_id and b.res_exc_id=c.exc_id";

i hope it works.

[raul66]

Ok, my code, which doesn't work.

//this only for time, which I want to print
<?php 
	$query="SELECT res_time FROM res";
	$result = mysql_query($query);
	$dbf = mysql_fetch_assoc($result);
?>	
<div class="main">
		<table cellspacing="20">
			<tr>
			<th></th>
			<th></th>
			<th>Count of good excercises</th>
			<th>Count of bad excercises</th>
			<th>Success</th>
			</tr>
	<tr><td><?php echo $dbf['res_time']; ?></td></tr>
	<?php
$query1="SELECT exc_excercise_name, res_result, usr_id FROM exc, res, usr WHERE res_exc_id = exc_id AND res_usr_id = usr_id";
$result1 = mysql_query($query1);
while (list($exc_excercise_name,$res_result) = mysql_fetch_row($result1)) {
    		echo "<tr>",
			"<td>",
			"</td>",
            "<td>",
            $exc_excercise_name,"</td>",
            "<td>",
            "</td>",
			"<td>",
            "</td>",
            "<td>",
            $res_result,
            "</td>",
        "</tr>";   
}
@mysql_close($connect);
			?>
		</table>

I want print ALL excercises and ALL his results from this excercises, which makes the schoolar in fixed time, when I click on his name on other search page. When he didn't make any excercises, his result list will be blank.

[djjjozsi]

lets test the suggested queries in phpmyadmin, and select the correct one. notice that the table names should changed.

(i've tested my query :) )

[raul66]

lets test the suggested queries in phpmyadmin, and select the correct one. notice that the table names should changed.

(i've tested my query :) )

A tested these queries, but no one works, how I want.
Could you show me the right one?

Thanks

[djjjozsi]

tell us the table names, or change the names from ours.

[raul66]

A tested these queries, but no one works, how I want.
Could you show me the right one?

Thanks

You are right, one of these queries was good for me.
It work's.

Thanks

[djjjozsi]

which one?

- i see now -