Problem with null query inner join

[gmc1103]

Hi

 

I have this query

SELECT 
COALESCE(id_tempo, 0) AS id_tempos
,`semana_dias`.`dia`
, `req_material_sala`.`sala`
, `req_material_tempo`.`inicio`
FROM sala_ocupacao 
INNER JOIN `semana_dias` ON (`sala_ocupacao`.`id_dia` = `semana_dias`.`id_dia`)
INNER JOIN `req_material_sala` ON (`sala_ocupacao`.`id_sala` = `req_material_sala`.`idsala`)
INNER JOIN `req_material_tempo` ON (`sala_ocupacao`.`id_tempo` = req_material_tempo.`idtempo`)
ORDER BY id_ocup;

That one returns only the values where there is no null

 

If i remove those lines

, `req_material_tempo`.`inicio`
INNER JOIN `req_material_tempo` ON (`sala_ocupacao`.`id_tempo` = req_material_tempo.`idtempo`)

I have the null values converted to zero but i don't have the column where i can get the names instead of the id

 

How can i achive this??

 

 

Something like the picture with the 0 or null values

 

 

Thanks for any help

 

 

[Barand]

You are selecting four columns but show only three in the output image ???

 

I don't know your data structure or content so can only  guess, but would a LEFT JOIN instead of INNER JOIN on `req_material_tempo` give the desired result?

[gmc1103]

Hi Barand

 

You again, thank you, it was the fix for my issue, now i have

 

 

 

You are the man

Best regards

[gmc1103]

But now i have a problem running my query in php file

<?php
error_reporting(E_ALL | E_NOTICE);
ini_set('display_errors', '1');
include 'conn.php';
mysql_query("SET NAMES 'utf8'");
$rs = mysql_query('SELECT 
`semana_dias`.`dia`
, `req_material_sala`.`sala`
, `req_material_tempo`.`inicio`
FROM sala_ocupacao 
INNER JOIN `semana_dias` ON (`sala_ocupacao`.`id_dia` = `semana_dias`.`id_dia`)
INNER JOIN `req_material_sala` ON (`sala_ocupacao`.`id_sala` = `req_material_sala`.`idsala`)
LEFT  JOIN `req_material_tempo` ON (`sala_ocupacao`.`id_tempo` = req_material_tempo.`idtempo`)
ORDER BY sala_ocupacao.`id_ocup`id_ocup;');
$result = array();
while($row = mysql_fetch_object($rs)){
	array_push($result, $row);
}
    echo json_encode($result);
?>

i get this error

 

</span> Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\multiple\get_ocupacao.php on line <i>16

 

???

 

Whats wrong now

i get this error

[Barand]

The last line of the query looks odd

 

...ORDER BY sala_ocupacao.`id_ocup`id_ocup;'