Posted by Hilarion on 11/02/05 19:49

> SELECT * FROM communication AS comm LEFT JOIN lnk_ent_comm AS lnk ON
> comm.id = lnk.id_communication where lnk.id_entity = 54
>
> What I get is:
> telephone home | 4567889
> cell phone | 456789
> email1 | blabla@bla.com
>
> What I want is:
> telephone home | 4567889
> fax |
> cell phone | 456789
> email1 | blabla@bla.com
> email2 |
> email3 |
> ...


The problem is in your WHERE clause. You are using lnk_ent_comm table
column in the where constraint, which eliminates all the results
that do not have corresponding entry in it.

Your solution is moving the WHERE clause contents into the LEFT JOIN
condition:

SELECT
comm.*,
lnk.value
FROM
communication AS comm LEFT OUTER JOIN
lnk_ent_comm AS lnk ON comm.id = lnk.id_communication AND lnk.id_entity = 54


Your solution may also be a cross joining entity with communication
and left joining lnk_ent_comm to the result on two fields (one with entity
and another with communication). This way you will be able to keep
the filtering in the WHERE clause and also output some entity data:

SELECT
comm.*,
lnk.value
FROM
entity AS en CROSS JOIN
communication AS comm LEFT OUTER JOIN
lnk_ent_comm AS lnk ON en.id = lnk.id_entity AND comm.id = lnk.id_communication
WHERE
en.id = 54


Hilarion

[Back to original message]