Posted by Pugi! on 11/02/05 20:08

"Hilarion" <hilarion@SPAM.op.SMIECI.pl> schreef in bericht
news:dkau4v$d3k$1@news.onet.pl...
>> 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


This stuff works. Thank you very much.
Question : how or where did you learn this. Could you recommend book(s) or
website ?

Pugi!

[Back to original message]