Stefano Bragaglia wrote:
> Jerry,
>
> thank you again for your kindness! I hope not to bother you with my
> questions
>
> Yes, what I proposed here is an academic exercise... and I hope it will grow
> into a working project to pass the exam...
>
>
>>OK, glossary contains a name. And synonims (sic) contains synonyms of
>>name being surname and nickname.
>>
>>But no place have you indicated that these two come from the same row.
>
>
> What do you mean? Referring to the following bit of code, I thought that the
> last line is doing the trick... I mean, doesn't the FOREIGN KEY clause
> actually bind each synonim with the same correct row in table "glossary"?
> If I'm still wrong, can you please show me how to indicate that objects
> comes from the same row?
>
> 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));
>
> I know I'm looking like the guy who looks at the finger when you point out
> something, but I really don't understand where is my error... Are you
> suggesting me to use a simple unique table to do the whole job? If so,
> yes... it could be easier to manage, but I also have to develop a little
> WAMP solution and I'm planning to handle things (synonims, for example) as
> list of strings and not as a single string to edit...
>
> Still thank you, forgive my dumbness,
> Stefano Bragaglia
>
>

In your SELECT statement you are. But there is NOTHING in your design
which does it. That's the problem.

These two rows:

INSERT INTO synonims VALUES (
'example', 'name', 'surname');

INSERT INTO synonims VALUES (
'example', 'name', 'nickname');

Are independent rows. There is nothing to tie 'surname' and 'nickname'
to the same row. They can easily point to two different rows.

It's the difference between database design and the data itself. And
it's why, in your first try, you got two 'person' entries returned - one
for surname and one for nickname. The result were correct - you were
referring to two 'person' entries.

Your problem is that your 'synonims' table 'options' field contains two
different types of data - a 'name' and a 'surname'.

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