Empty id in MySQL, cannot show null answers

I got this, and I want to get their "company" names for each one.

SELECT `client`.`name`,`client`.`lastname`
FROM `check`,`reserv`,`client`
WHERE `check`.`idReserv`=`reserv`.`id`
AND `reserv`.`idPerson`=`client`.`id`
ORDER BY `check`.`id`

, and I want to get their "company" names for each one, from table "company".

So I tried this:

SELECT `client`.`name`,`client`.`lastname`, `company`.`name`
FROM `check`,`reserv`,`client`,`company`
WHERE `reserv`.`idCompany`=`company`.`id`
AND `check`.`idReserv`=`reserv`.`id`
AND `reserv`.`idPerson`=`client`.`id`
ORDER BY `check`.`id`

but there is some people in the table "reserv" with an "idCompany" inexistent. so with that condition, this query only show me people who has an existent "id" in the table "company". I want to show the people with no company up and the space of company.name in blank if there is no company

I tryed many ways even with joins, but I cannot fix it. I'm tired to write "company" also.

Solution

The ANSI 89 standard uses , notation for table joins with the criteria of the join being in the where clause. However I don't believe mySQL supports this outer style of join needed to address your problem. To express an outer join in this syntax you would need to use a *= for left join or =* for a right join; but again not sure mySQL supports it.

So in your case:

SELECT `client`.`name`,`client`.`lastname`, `company`.`name`
FROM `check`,`reserv`,`client`,`company`
WHERE `reserv`.`idCompany`*=`company`.`id`
  AND `check`.`idReserv`=`reserv`.`id`
  AND `reserv`.`idPerson`=`client`.`id`
ORDER BY `check`.`id`

However, I find that notation difficult to read and no need for all the escaping of table/column names (except reserved words)... so the below follows the ANSI 92 standards which allow for the use of INNER and LEFT Join syntax to explicitly define the type of join. Both notations should optimize to the same execution plan so either works (provided mySQL supports the *= notation) as well; it's just a matter of which standard you choose to use.

SELECT client.name
     , client.lastname
     , company.name
FROM `check`
INNER JOIN reserv
  on `check`.idReserv=reserv.id
INNER JOIN client
  on reserv.idPerson=client.id
LEFT JOIN company
  on reserv.idCompany=company.id
ORDER BY `check`.id