Empty id in MySQL, cannot show null answers

Question

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.

Welcome to Stack Overflow. You can format source code with the [Code Sample `{}`](https://i.imgur.com/3ppJhiE.png) toolbar button—I've done it for you this time. You also have a preview pane right below the editor so you can see how changes look like before you publish them. — Álvaro González, Jun 19 '17 at 15:27
Additional tip: we know you need help, so "please help" and other such messages will sometimes be read as begging, and that may put people off assisting you. Additionally, it is clearer if you can use real words, rather than txtspk. "Please" surely is not more effort to type than "plz". Thanks! — halfer, Jun 19 '17 at 15:29

score 1 · Answer 1 · answered Jun 19 '17 at 15:35

1

You can use LEFT JOIN for this purpose like-

reserv r LEFT JOIN company c ON r.idCompany = c.id

answered Jun 19 '17 at 15:35

Keyur Panchal

1,382
1
10
15

Since you can't mix standards in the join, it would be nice to spell out the whole query as this alone will not do the trick. – xQbert Jun 19 '17 at 15:42

Eky Pratama · Answer 2 · 2017-06-19T15:48:23.447

1

You should use LEFT join instead.

SQL LEFT JOIN

SELECT c.name, c.lastname, co.name
FROM check AS ck
LEFT JOIN reserv AS r ON(ck.idReserv = r.id)
LEFT JOIN client AS c ON(r.idPerson = c.id)
LEFT JOIN company AS co ON(r.idCompany = co.id)
ORDER BY c.id

edited Jun 19 '17 at 15:48

answered Jun 19 '17 at 15:39

Eky Pratama

11
4

Not sure they all need left join; but that wasn't spelled out. and I think check is a [reserved word](https://dev.mysql.com/doc/refman/5.5/en/keywords.html) so it would need the backticks I believe. – xQbert Jun 19 '17 at 15:49
@xQbert he may figure what he needs, at least this may help his problem. He may do INNER JOIN, if he really wanted too. – Eky Pratama Jun 19 '17 at 15:52

xQbert · Accepted Answer · 2017-06-19T16:09:27.367

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

thank you so much, your answer is pretty complete. You are the best — Feber Castellon, Jun 19 '17 at 20:44

Empty id in MySQL, cannot show null answers

3 Answers3