Stefano Bragaglia wrote:
> 'morning everyone!
>
> I hope not to violate any rule by posing this question... If so, I'm sorry
> and please forgive me! I searched Internet and books for a solution with no
> success, this is why I'm asking you an hint...
>
> Let me briefly expose my problem: I'm a software engineer student and I'm
> working on a demonstrative project to discuss it during my exam...
>
> I focused on glossary, one of the documents produced during the developing
> process of a database. It's the transposition in tabular form of concepts
> expressed in natural language and gathered during the analysis phase of the
> project. The glossary table contains entries (names of concepts),
> description (description of concepts), synonims' list (can be blank), list
> of entries linked to the current one (can't be blank). I decided to keep
> things simple so I simply added the concept of project's names to permit the
> handling of multiple glossary.
>
> I passed by each step of the developing process quite easily, but when i
> finally checked out the correctness of my work I discovered that something
> was wrong... I tryed to fix it but with no success... I'm going to report
> the SQL statements I wrote and the resulting table affected by the problem:
>
>
>
>
> First of all, I created the data structures:
> ============================================
>
> CREATE SCHEMA archive;
>
> USE archive;
>
> CREATE TABLE projects (
> name VARCHAR(50) NOT NULL,
> PRIMARY KEY (name));
>
> CREATE TABLE glossary (
> name VARCHAR(50) NOT NULL,
> entry VARCHAR(50) NOT NULL,
> decription TEXT NOT NULL,
> PRIMARY KEY (name, entry),
> FOREIGN KEY (name) REFERENCES projects (name));
>
> CREATE TABLE synonims (
> name VARCHAR(50) NOT NULL,
> entry VARCHAR(50) NOT NULL,
> option VARCHAR(50) NOT NULL,
> PRIMARY KEY (name, entry, option),
> FOREIGN KEY (name, entry) REFERENCES glossary (name, entry));
>
> CREATE TABLE linking (
> name VARCHAR(50) NOT NULL,
> entry VARCHAR(50) NOT NULL,
> target VARCHAR(50) NOT NULL,
> PRIMARY KEY (name, entry, target),
> FOREIGN KEY (name, entry) REFERENCES glossary (name, entry),
> FOREIGN KEY (name, target) REFERENCES glossary (name, entry));
>
>
>
> Afterwards I populated the tables with a few sample data:
> ================================================== =======
>
> INSERT INTO projects VALUES (
> 'example');
>
> INSERT INTO glossary VALUES (
> 'example', 'person', 'example of a typical entity');
>
> INSERT INTO glossary VALUES (
> 'example', 'name', 'example of a typical attribute');
>
> INSERT INTO synonims VALUES (
> 'example', 'name', 'surname');
>
> INSERT INTO synonims VALUES (
> 'example', 'name', 'nickname');
>
> INSERT INTO linking VALUES (
> 'example', 'person', 'name');
>
>
>
> Finally I developed the SQL statement to get the desired table:
> ================================================== =============
>
> SELECT g.entry, g.description,
> GROUP_CONCAT(s.option SEPARATOR ', ') AS options,
> GROUP_CONCAT(c.target SEPARATOR ', ') AS links
> FROM glossary g NATURAL LEFT JOIN synonims s JOIN linking l
> ON g.name = l.name AND g.entry =
> WHERE g.name = 'example'
> GROUP BY l.name, l.entry
> UNION
> SELECT g.entry, g.description,
> GROUP_CONCAT(s.option SEPARATOR ', '),
> GROUP_CONCAT(c.target SEPARATOR ', ') AS links
> FROM glossary g NATURAL LEFT JOIN synonims s JOIN linking l
> WHERE g.name = 'example'
> GROUP BY l.name, l.target;
>
>
>
> And this is what the above SQL statement produces:
> ==================================================
>
> +--------+------------------------------+-------------------+----------------+
> | entry | description | options | links
> |
> +--------+------------------------------+-------------------+----------------+
> | person | example of a typical entity | NULL | name
> |
> | name | example of typical attribute | surname, nickname | person, person
> | (*)
> +--------+------------------------------+-------------------+----------------+
>
> As you can see, the 'links' field of the record whose entry in 'name'
> contains a double reference to 'person' (*) while I expected a single
> reference...
>
> Does anyone can help? Thank you all in advance, please let me know,
>
> Stefano Bragaglia
>
>

As it should. You have both surname and nickname in your options, and
each is a link to a person. And you show two links.

There is nothing in your setup which indicates surname and nickname must
belong to the same person.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================