Posted by Pugi! on 11/02/05 18:28

I have problem with sql statement.
I have made a address-database, problem is concerning getting information
out of it.
Consider following tables:
table entity
id (primary key), lastname, firstname, title, sex (Male, Female,
Organization)

table communication
id (primary key), type, abbreviation
type = telephone home, telephone work, fax home, fax work, cell phone,
email home 1, email home 2 , email home 3, email work, personal website,
website work, ...

table lnk_ent_comm
id_entity (primary key), id_communication (primary key), value

I think relations are clear ?

Because for not everyone in my address-db I have all that information I
created the table lnk_ent_comm
When I query database for a certain entity I get all the types of how to
communicate with him/her/it that are available. But I would also like to see
the types/means of communication that are missing for which there is no
entry in table lnk_ent_comm
I tried a left join from table communication on lnk_ent_comm, but that
doesn't work.

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 |
....

I think it must be possible, but I can't remember how. Oh yes, I am using
MySQL.

thanx,

Pugi!

[Back to original message]