Problem with obtaining data from database (select statement)

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!

Re: Problem with obtaining data from database (select statement)

Pugi! wrote:
> I have problem with sql statement.
> I have made a address-database, problem is concerning getting information
> out of it.

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

Join your communication table to the lnk_ent_comm table and search for the
user id_entity and where lnk_ent_comm.value is NULL.


//Aho

Re: Problem with obtaining data from database (select statement)

"J.O. Aho" schreef in bericht
news:3ss896Fpjj4pU1@individual.net...
> Pugi! wrote:
>> I have problem with sql statement.
>> I have made a address-database, problem is concerning getting information
>> out of it.
>
>> 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.
>
> Join your communication table to the lnk_ent_comm table and search for the
> user id_entity and where lnk_ent_comm.value is NULL.
>
>
> //Aho

How do u do that? Could u give example ? Remember there are no null values
in lnk_ent_comm.

Pugi!

Re: Problem with obtaining data from database (select statement)

Pugi! wrote:
> "J.O. Aho" schreef in bericht
> news:3ss896Fpjj4pU1@individual.net...
>> Pugi! wrote:
>>> I have problem with sql statement.
>>> I have made a address-database, problem is concerning getting information
>>> out of it.
>>> 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.
>>
>>
>> Join your communication table to the lnk_ent_comm table and search for the
>> user id_entity and where lnk_ent_comm.value is NULL.
>
> How do u do that? Could u give example ? Remember there are no null values
> in lnk_ent_comm.

SELECT * FROM communication LEFT JOIN lnk_ent_comm ON
communication.id = lnk_ent_comm.id_communication WHERE lnk_ent_comm.id_entity
= 54 AND lnk_ent_comm IS NULL


//Aho

Re: Problem with obtaining data from database (select statement)

"J.O. Aho" schreef in bericht
news:3ssbbuFppjkrU1@individual.net...
> Pugi! wrote:
>> "J.O. Aho" schreef in bericht
>> news:3ss896Fpjj4pU1@individual.net...
>>> Pugi! wrote:
>>>> I have problem with sql statement.
>>>> I have made a address-database, problem is concerning getting
>>>> information
>>>> out of it.
>>>> 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.
>>>
>>>
>>> Join your communication table to the lnk_ent_comm table and search for
>>> the
>>> user id_entity and where lnk_ent_comm.value is NULL.
>>
>> How do u do that? Could u give example ? Remember there are no null
>> values
>> in lnk_ent_comm.
>
> SELECT * FROM communication LEFT JOIN lnk_ent_comm ON
> communication.id = lnk_ent_comm.id_communication WHERE
> lnk_ent_comm.id_entity
> = 54 AND lnk_ent_comm IS NULL
>
>
> //Aho

If I run it as above I get error : #1054 - Unknown column 'lnk_ent_comm' in
'where clause'.
If I add a valid columname like value : query returns nothing.

Pugi!

Re: Problem with obtaining data from database (select statement)

> 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

Re: Problem with obtaining data from database (select statement)

"Hilarion" 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!

Re: Problem with obtaining data from database (select statement)

> This stuff works. Thank you very much.

No problem.


> Question : how or where did you learn this.

Some basics at a university and four years of using SQL.


> Could you recommend book(s) or website ?

I'm affraid I do not remember any. If I need something
specific then I look for it in a manual for the specific
SQL dialect I'm dealing with. General rules are (almost)
same in all SQL servers (for SELECT statements - in case
of DML, which means INSERT, DELETE and UPDATE only
the basics are same and in case of DDL, which means
CREATE, DROP and ALTER it gets even more complicated).


I suggest looking at any basic SQL tutorial which is
giving many examples of different JOINs and ways of using
them together. It is my way of learning - understand
the basics of all mechanisms and then learn the advanced
parts using not only definitions and descriptions but
also many many many examples (including exercises).


Also remember that you will never learn everything about
SQL and you will never need to know everything about
SQL. When you'll learn a lot, then you will know where
to look for specific things you need but do not know
and how to learn fast to use them.


Hilarion

Re: Problem with obtaining data from database (select statement)

Pugi! wrote:
> "J.O. Aho" schreef in bericht
> news:3ssbbuFppjkrU1@individual.net...
>> Pugi! wrote:
>>> "J.O. Aho" schreef in bericht
>>> news:3ss896Fpjj4pU1@individual.net...
>>>> Pugi! wrote:
>>>>> I have problem with sql statement.
>>>>> I have made a address-database, problem is concerning getting
>>>>> information
>>>>> out of it.
>>>>> 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.
>>>>
>>>> Join your communication table to the lnk_ent_comm table and search for
>>>> the
>>>> user id_entity and where lnk_ent_comm.value is NULL.
>>> How do u do that? Could u give example ? Remember there are no null
>>> values
>>> in lnk_ent_comm.
>> SELECT * FROM communication LEFT JOIN lnk_ent_comm ON
>> communication.id = lnk_ent_comm.id_communication WHERE
>> lnk_ent_comm.id_entity
>> = 54 AND lnk_ent_comm IS NULL
>
> If I run it as above I get error : #1054 - Unknown column 'lnk_ent_comm' in
> 'where clause'.
> If I add a valid columname like value : query returns nothing.

sorry, but it should have been lnk_ent_comm.value


//Aho