Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.mysql > #5489 > unrolled thread

select problem

Back to article view | Back to comp.databases.mysql

Contents

  select problem Onno Dreischor <onno@ongeldig.nl> - 2014-06-25 19:19 +0200
    Re: select problem Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2014-06-25 19:53 +0200
      Re: select problem Onno Dreischor <onno@ongeldig.nl> - 2014-06-25 23:02 +0200
      Re: select problem Onno Dreischor <onno@ongeldig.nl> - 2014-06-25 23:06 +0200
        Re: select problem Geoff Muldoon <geoff.muldoon@invalid.invalid> - 2014-06-26 09:18 +1000
          Re: select problem Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-26 00:48 +0000
            Re: select problem Geoff Muldoon <geoff.muldoon@invalid.invalid> - 2014-06-26 11:11 +1000
            Re: select problem Jerry Stuckle <jstucklex@attglobal.net> - 2014-06-25 22:50 -0400
              Re: select problem Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-26 06:52 +0000
                Re: select problem gordonb.dfadr@burditt.org (Gordon Burditt) - 2014-06-26 04:41 -0500
                  Re: select problem Doug Miller <doug_at_milmac_dot_com@example.com> - 2014-06-26 11:09 +0000
                  Re: select problem Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-06-27 11:01 +0200
    Re: select problem Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-25 21:45 +0000
    Re: select problem Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-06-27 09:00 +0200
      Re: select problem Onno Dreischor <onno@ongeldig.nl> - 2014-06-27 20:06 +0200
        Re: select problem Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-06-27 23:03 +0200
          Re: select problem "Erick T. Barkhuis" <erick.use-net@ardane.c.o.m> - 2014-06-28 13:00 +0000
        Re: select problem Jerry Stuckle <jstucklex@attglobal.net> - 2014-06-28 22:36 -0400

#5489 — select problem

I have made a small MySQL-database about persons figuring in a series of 
little stories. The structure is, given as TABLE(field1,field2):
PERSON (pid, name)
STORY (sid, title)
LOCALISATION (lid, pid, sid).
Now I'm trying to write a query giving the stories where two given 
persons appear. Somehow I feel that this is rather easy, but in a way 
I'm blind for that. As I see it the problem is in fact having two 
variables in the same table.
Am I right and how can I tackle this problem.
Many thanks in advance,
Onno

[toc] | [next] | [standalone]

#5490

On 06/25/2014 07:19 PM, Onno Dreischor wrote:
> I have made a small MySQL-database about persons figuring in a series of 
> little stories. The structure is, given as TABLE(field1,field2):
> PERSON (pid, name)
> STORY (sid, title)
> LOCALISATION (lid, pid, sid).
> Now I'm trying to write a query giving the stories where two given 
> persons appear.

I assume that the persons should appear in the same story. One way is to
use a join:

select a.sid
from LOCALISATION a
join LOCALISATION b
    on a.sid = b.sid
where a.pid = ?
and b.pid = ?;

> Somehow I feel that this is rather easy, but in a way 
> I'm blind for that. As I see it the problem is in fact having two 
> variables in the same table.

I'm not sure what you mean, can you elaborate?


/Lennart

[toc] | [prev] | [next] | [standalone]

#5493

Lennart Jonsson schreef op 25-6-2014 19:53:
> On 06/25/2014 07:19 PM, Onno Dreischor wrote:
>> I have made a small MySQL-database about persons figuring in a series of
>> little stories. The structure is, given as TABLE(field1,field2):
>> PERSON (pid, name)
>> STORY (sid, title)
>> LOCALISATION (lid, pid, sid).
>> Now I'm trying to write a query giving the stories where two given
>> persons appear.
>
> I assume that the persons should appear in the same story. One way is to
> use a join:
>
> select a.sid
> from LOCALISATION a
> join LOCALISATION b
>      on a.sid = b.sid
> where a.pid = ?
> and b.pid = ?;
>
>> Somehow I feel that this is rather easy, but in a way
>> I'm blind for that. As I see it the problem is in fact having two
>> variables in the same table.
>
> I'm not sure what you mean, can you elaborate?
>
>
> /Lennart
>
>
I guess in the WHERE part you only can state one item: SELECT ... WHERE 
person.name='figure 1'. Indeed the persons should appear in the same 
story. I have been thinking about a join, but without result. I'll try 
this one.
Thank you.
Onno

[toc] | [prev] | [next] | [standalone]

#5494

Erick T. Barkhuis schreef op 25-6-2014 19:59:
> Lennart Jonsson:
>
>> On 06/25/2014 07:19 PM, Onno Dreischor wrote:
>>> I have made a small MySQL-database about persons figuring in a
>>> series of  little stories. The structure is, given as
>>> TABLE(field1,field2):   PERSON (pid, name)
>>> STORY (sid, title)
>>> LOCALISATION (lid, pid, sid).
>>> Now I'm trying to write a query giving the stories where two given
>>> persons appear.
>
> Do you have their ID, or perhaps just a name?
>
> How do you want 'stories' returned? Title or just ID?
> In what order?
>
>>
>> I assume that the persons should appear in the same story. One way is
>> to use a join:
>>
>> select a.sid
>>from LOCALISATION a
>> join LOCALISATION b
>>     on a.sid = b.sid
>
> AND a.lid <> b.lid       // else you get the same row twice
>
>
>> where a.pid = ?
>> and b.pid = ?;
>
>
>
I want the stories as well as the persons returned in name. That is 
hardly a problem. If you have the ID, you have the other part as well.
You both gave me a good hint, thank you.
Onno

[toc] | [prev] | [next] | [standalone]

#5496

> >> On 06/25/2014 07:19 PM, Onno Dreischor wrote:
> >>> I have made a small MySQL-database about persons figuring in a
> >>> series of  little stories. The structure is, given as
> >>> TABLE(field1,field2):   PERSON (pid, name)
> >>> STORY (sid, title)
> >>> LOCALISATION (lid, pid, sid).
> >>> Now I'm trying to write a query giving the stories where two given
> >>> persons appear.

> I want the stories as well as the persons returned in name. That is 
> hardly a problem. If you have the ID, you have the other part as well.
> You both gave me a good hint, thank you.

Continue to use multiple aliased table joins:

select
  S.title,
  P1.name first_name,
  P2.name second_name
from STORY S
inner join LOCATIONISATION L1 on L1.sid = S.sid
  inner join PERSON P1 on P1.pid = L1.pid
inner join LOCATIONISATION L2 on L2.sid = S.sid
  inner join PERSON P2 on P2.pid = L2.pid
where P1.name = '<var1>'
and P2.name = '<var2>';

[toc] | [prev] | [next] | [standalone]

#5497

On Thu, 26 Jun 2014 09:18:56 +1000, Geoff Muldoon wrote:

>> >> On 06/25/2014 07:19 PM, Onno Dreischor wrote:
>> >>> I have made a small MySQL-database about persons figuring in a
>> >>> series of  little stories. The structure is, given as
>> >>> TABLE(field1,field2):   PERSON (pid, name)
>> >>> STORY (sid, title)
>> >>> LOCALISATION (lid, pid, sid).
>> >>> Now I'm trying to write a query giving the stories where two given
>> >>> persons appear.
> 
>> I want the stories as well as the persons returned in name. That is
>> hardly a problem. If you have the ID, you have the other part as well.
>> You both gave me a good hint, thank you.
> 
> Continue to use multiple aliased table joins:
> 
> select
>   S.title,
>   P1.name first_name,
>   P2.name second_name
> from STORY S inner join LOCATIONISATION L1 on L1.sid = S.sid
>   inner join PERSON P1 on P1.pid = L1.pid
> inner join LOCATIONISATION L2 on L2.sid = S.sid
>   inner join PERSON P2 on P2.pid = L2.pid
> where P1.name = '<var1>'
> and P2.name = '<var2>';

I think the OP is overthinking - he may not need to pull the names from 
the result if he's specifying them in the query, which means he only 
needs to find the title.

select
   S.title,
   '<var_1>' first_name,
   '<var_2>' second_name
.....

So given tables:

mysql> describe person;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| pid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(15) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> describe localisation;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| pid   | int(11) | YES  |     | NULL    |       |
| sid   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe story;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| title | varchar(40) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> 

which have been populated with some random data (10 people, 2 per story, 
16*15 story titles), and view:

create view instory as select localisation.sid, person.name from 
localisation join person on localisation.pid = person.pid;

Then:

select title, 'Helen' as name_1, 'Andrew' as name_2 from story where sid 
in (select sid from instory where name = 'Helen') and sid in (select sid 
from instory where name = 'Andrew');

+---------------------+-------+--------+
| title               | Helen | Andrew |
+---------------------+-------+--------+
| The Coldest Car     | Helen | Andrew |
| The Slowest Bus     | Helen | Andrew |
| The Bigest Airplane | Helen | Andrew |
| The Fastest Ship    | Helen | Andrew |
| The Coldest Yacht   | Helen | Andrew |
| The Coldest Balloon | Helen | Andrew |
| The Oldest Balloon  | Helen | Andrew |
| The Crawling Planet | Helen | Andrew |
| The Rotten Building | Helen | Andrew |
| The Slimy Cinema    | Helen | Andrew |
+---------------------+-------+--------+

-- 
Denis McMahon, denismfmcmahon@gmail.com

[toc] | [prev] | [next] | [standalone]

#5498

denismfmcmahon@gmail.com says...

> > select
> >   S.title,
> >   P1.name first_name,
> >   P2.name second_name
> > from STORY S inner join LOCATIONISATION L1 on L1.sid = S.sid
> >   inner join PERSON P1 on P1.pid = L1.pid
> > inner join LOCATIONISATION L2 on L2.sid = S.sid
> >   inner join PERSON P2 on P2.pid = L2.pid
> > where P1.name = '<var1>'
> > and P2.name = '<var2>';
> 
> I think the OP is overthinking - he may not need to pull the names from 
> the result if he's specifying them in the query, which means he only 
> needs to find the title.

The above could easily be modified - I often try to cater for future 
scenarios - to handle returning multiple result sets, e.g.:

where P1.name = 'Fred'
and P2.name in ('Jane', 'June')

> select title, 'Helen' as name_1, 'Andrew' as name_2 from story where 
> sid in (select sid from instory where name = 'Helen') and sid in 
> (select sid from instory where name = 'Andrew');

Depending on you table sizes and indexing strategy, using multiple 'in' 
and sub-selects might not necessarily be A Good Thing.

Horses for courses, more than one way to skin a cat, must stop using 
cliches.

GM

[toc] | [prev] | [next] | [standalone]

#5499

On 6/25/2014 8:48 PM, Denis McMahon wrote:
> On Thu, 26 Jun 2014 09:18:56 +1000, Geoff Muldoon wrote:
> 
>>>>> On 06/25/2014 07:19 PM, Onno Dreischor wrote:
>>>>>> I have made a small MySQL-database about persons figuring in a
>>>>>> series of  little stories. The structure is, given as
>>>>>> TABLE(field1,field2):   PERSON (pid, name)
>>>>>> STORY (sid, title)
>>>>>> LOCALISATION (lid, pid, sid).
>>>>>> Now I'm trying to write a query giving the stories where two given
>>>>>> persons appear.
>>
>>> I want the stories as well as the persons returned in name. That is
>>> hardly a problem. If you have the ID, you have the other part as well.
>>> You both gave me a good hint, thank you.
>>
>> Continue to use multiple aliased table joins:
>>
>> select
>>   S.title,
>>   P1.name first_name,
>>   P2.name second_name
>> from STORY S inner join LOCATIONISATION L1 on L1.sid = S.sid
>>   inner join PERSON P1 on P1.pid = L1.pid
>> inner join LOCATIONISATION L2 on L2.sid = S.sid
>>   inner join PERSON P2 on P2.pid = L2.pid
>> where P1.name = '<var1>'
>> and P2.name = '<var2>';
> 
> I think the OP is overthinking - he may not need to pull the names from 
> the result if he's specifying them in the query, which means he only 
> needs to find the title.
> 
> select
>    S.title,
>    '<var_1>' first_name,
>    '<var_2>' second_name
> .....
> 
> So given tables:
> 
> mysql> describe person;
> +-------+-------------+------+-----+---------+----------------+
> | Field | Type        | Null | Key | Default | Extra          |
> +-------+-------------+------+-----+---------+----------------+
> | pid   | int(11)     | NO   | PRI | NULL    | auto_increment |
> | name  | varchar(15) | YES  |     | NULL    |                |
> +-------+-------------+------+-----+---------+----------------+
> 2 rows in set (0.00 sec)
> 
> mysql> describe localisation;
> +-------+---------+------+-----+---------+-------+
> | Field | Type    | Null | Key | Default | Extra |
> +-------+---------+------+-----+---------+-------+
> | pid   | int(11) | YES  |     | NULL    |       |
> | sid   | int(11) | YES  |     | NULL    |       |
> +-------+---------+------+-----+---------+-------+
> 2 rows in set (0.00 sec)
> 
> mysql> describe story;
> +-------+-------------+------+-----+---------+----------------+
> | Field | Type        | Null | Key | Default | Extra          |
> +-------+-------------+------+-----+---------+----------------+
> | sid   | int(11)     | NO   | PRI | NULL    | auto_increment |
> | title | varchar(40) | YES  |     | NULL    |                |
> +-------+-------------+------+-----+---------+----------------+
> 2 rows in set (0.00 sec)
> 
> mysql> 
> 
> which have been populated with some random data (10 people, 2 per story, 
> 16*15 story titles), and view:
> 
> create view instory as select localisation.sid, person.name from 
> localisation join person on localisation.pid = person.pid;
> 
> Then:
> 
> select title, 'Helen' as name_1, 'Andrew' as name_2 from story where sid 
> in (select sid from instory where name = 'Helen') and sid in (select sid 
> from instory where name = 'Andrew');
> 
> +---------------------+-------+--------+
> | title               | Helen | Andrew |
> +---------------------+-------+--------+
> | The Coldest Car     | Helen | Andrew |
> | The Slowest Bus     | Helen | Andrew |
> | The Bigest Airplane | Helen | Andrew |
> | The Fastest Ship    | Helen | Andrew |
> | The Coldest Yacht   | Helen | Andrew |
> | The Coldest Balloon | Helen | Andrew |
> | The Oldest Balloon  | Helen | Andrew |
> | The Crawling Planet | Helen | Andrew |
> | The Rotten Building | Helen | Andrew |
> | The Slimy Cinema    | Helen | Andrew |
> +---------------------+-------+--------+
> 

Actually, I think he needs the names - since he is linking on 'pid'
instead of name.

For instance - what if he had two different people with the name 'Helen'?

His database definition accounts for that possibility.


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

[toc] | [prev] | [next] | [standalone]

#5500

On Wed, 25 Jun 2014 22:50:05 -0400, Jerry Stuckle wrote:

> Actually, I think he needs the names - since he is linking on 'pid'
> instead of name.

I meant he doesn't need to select fields in the result that he's 
supplying values for in where clauses.

Consider a richardian database[1] of fields (title, actor1, actor2):

select title, actor1, actor2 from stories where actor1 = 'fred' and 
actor2 = 'jim';

will actor1 and actor2 in the result record set ever have values other 
than "fred" and "jim" as specified in the query?

[1] Sorry about the headache.

-- 
Denis McMahon, denismfmcmahon@gmail.com

[toc] | [prev] | [next] | [standalone]

#5501

> Consider a richardian database[1] of fields (title, actor1, actor2):
> 
> select title, actor1, actor2 from stories where actor1 = 'fred' and 
> actor2 = 'jim';
> 
> will actor1 and actor2 in the result record set ever have values other 
> than "fred" and "jim" as specified in the query?

They might contain those names incorrectly capitalized, as in 'fREd'
and 'jiM', assuming a case-insensitive comparison is being done.

> [1] Sorry about the headache.

[toc] | [prev] | [next] | [standalone]

#5502

gordonb.dfadr@burditt.org (Gordon Burditt) wrote in 
news:f9OdnXyxeZkgdzbOnZ2dnUVZ_gOdnZ2d@posted.internetamerica:

>> Consider a richardian database[1] of fields (title, actor1, actor2):
>> 
>> select title, actor1, actor2 from stories where actor1 = 'fred' and 
>> actor2 = 'jim';
>> 
>> will actor1 and actor2 in the result record set ever have values other 
>> than "fred" and "jim" as specified in the query?
> 
> They might contain those names incorrectly capitalized, as in 'fREd'
> and 'jiM', assuming a case-insensitive comparison is being done.

So change the command to
SELECT title, LOWER(actor1), LOWER(actor2) ...

[toc] | [prev] | [next] | [standalone]

#5506

Gordon Burditt wrote:

> [Denis McMahon wrote:]
>> Consider a richardian database[1] of fields (title, actor1, actor2):
>> 
>> select title, actor1, actor2 from stories where actor1 = 'fred' and
>> actor2 = 'jim';
>> 
>> will actor1 and actor2 in the result record set ever have values other
>> than "fred" and "jim" as specified in the query?
> 
> They might contain those names incorrectly capitalized, as in 'fREd'
> and 'jiM', assuming a case-insensitive comparison is being done.

Also, even then there could be actor1 = 'jim' and actor2 = 'fred' instead.  
In such a table, column order need not signify importance, nor would 
importance be necessarily important for a query.  As a result, you would 
have to use several ORs or INs if you did not care about importance, which 
can quickly become unmaintainable [e.g., P(5, 5) = 5!/((5 − 5)!) = 120].

Do not do this, ever.

-- 
PointedEars

Twitter: @PointedEars2
Please do not Cc: me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [next] | [standalone]

#5495

On Wed, 25 Jun 2014 19:19:18 +0200, Onno Dreischor wrote:

> I have made a small MySQL-database about persons figuring in a series of
> little stories. The structure is, given as TABLE(field1,field2):
> PERSON (pid, name)
> STORY (sid, title)
> LOCALISATION (lid, pid, sid).
> Now I'm trying to write a query giving the stories where two given
> persons appear. Somehow I feel that this is rather easy, but in a way
> I'm blind for that. As I see it the problem is in fact having two
> variables in the same table.
> Am I right and how can I tackle this problem.
> Many thanks in advance,

It looks to me as if localisation is just a table to link characters to 
stories. As such, it prpobably doesn't need an id, and it's key would be 
the combination of the fields sid and pid.

First you need two sub queries. As both the sub-queries access the same 
join, you might want to define that as a view:

The view might be:

create view instory as select localisation.sid, person.name from 
localisation join person on localisation.pid = person.pid

The sub queries might be of the form:

select sid from instory where name = 'name'

and the main query could be of the form:

select title from story where sid in (subquery1) and sid in (subquery2)

Note that there might be some non mysql specifics in the above, I tend to 
do noddy testing with sqlite3 these days as it's quicker to knock up 
stuff for that in python ;)

-- 
Denis McMahon, denismfmcmahon@gmail.com

[toc] | [prev] | [next] | [standalone]

#5504

Onno Dreischor wrote:

> I have made a small MySQL-database about persons figuring in a series of
> little stories. The structure is, given as TABLE(field1,field2):
> PERSON (pid, name)
> STORY (sid, title)
> LOCALISATION (lid, pid, sid).
> Now I'm trying to write a query giving the stories where two given
> persons appear. Somehow I feel that this is rather easy, but in a way
> I'm blind for that. As I see it the problem is in fact having two
> variables in the same table.
> Am I right and how can I tackle this problem.

Why would a localisation that is related to the same story but different 
person, or to the same people but a different story, be a different 
localisation, having a different `lid`?

You need a table that relates people with stories, e.g. `story_person`.  If 
so, its primary key should be a multi-column key containing both `sid` and 
`pid` (in order to avoid duplicate relations at least).

[Then ISTM you need *another* table that relates a localisation to a story. 
But that has nothing to do with your question.]

If you have `story_person`, you can use logical operators in the WHERE 
clause, GROUP the result BY story and filter for the stories HAVING exactly 
two of people you are looking for (because the OR also gives you the story 
even if only one of them appears):

  SELECT `sid`
  FROM `story_person`
  WHERE ((`sid` = 42) AND ((`pid` = 23) OR (`pid` = 1337)))
  GROUP BY `sid`
  HAVING (COUNT(`pid`) = 2)

or 

  …
  WHERE ((`sid` = 42) AND (`pid` IN (23, 1337))
  …

If you also need the story title, you would need to JOIN with the `story` 
table to get the values for the `title` field.

If you need the `name`s of the people in each matching story, or filter by 
them instead of their `pid`s, you would need to JOIN also with the `person` 
table, and you can use the GROUP_CONCAT() function to return the names of 
the people as a list in one result field, for example as a comma-separated 
list.

In this case you do not necessarily need subqueries nor need you to join 
`story_person` with itself as the others have suggested.

<http://dev.mysql.com/doc/>

-- 
PointedEars

Twitter: @PointedEars2
Please do not Cc: me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [next] | [standalone]

#5507

Thomas 'PointedEars' Lahn schreef op 27-6-2014 9:00:
> Onno Dreischor wrote:
>
>> I have made a small MySQL-database about persons figuring in a series of
>> little stories. The structure is, given as TABLE(field1,field2):
>> PERSON (pid, name)
>> STORY (sid, title)
>> LOCALISATION (lid, pid, sid).
>> Now I'm trying to write a query giving the stories where two given
>> persons appear. Somehow I feel that this is rather easy, but in a way
>> I'm blind for that. As I see it the problem is in fact having two
>> variables in the same table.
>> Am I right and how can I tackle this problem.
>
> Why would a localisation that is related to the same story but different
> person, or to the same people but a different story, be a different
> localisation, having a different `lid`?
>
> You need a table that relates people with stories, e.g. `story_person`.  If
> so, its primary key should be a multi-column key containing both `sid` and
> `pid` (in order to avoid duplicate relations at least).
>
> [Then ISTM you need *another* table that relates a localisation to a story.
> But that has nothing to do with your question.]
>
> If you have `story_person`, you can use logical operators in the WHERE
> clause, GROUP the result BY story and filter for the stories HAVING exactly
> two of people you are looking for (because the OR also gives you the story
> even if only one of them appears):
>
>    SELECT `sid`
>    FROM `story_person`
>    WHERE ((`sid` = 42) AND ((`pid` = 23) OR (`pid` = 1337)))
>    GROUP BY `sid`
>    HAVING (COUNT(`pid`) = 2)
>
> or
>
>    …
>    WHERE ((`sid` = 42) AND (`pid` IN (23, 1337))
>    …
>
> If you also need the story title, you would need to JOIN with the `story`
> table to get the values for the `title` field.
>
> If you need the `name`s of the people in each matching story, or filter by
> them instead of their `pid`s, you would need to JOIN also with the `person`
> table, and you can use the GROUP_CONCAT() function to return the names of
> the people as a list in one result field, for example as a comma-separated
> list.
>
> In this case you do not necessarily need subqueries nor need you to join
> `story_person` with itself as the others have suggested.
>
> <http://dev.mysql.com/doc/>
>
To make some things clear:
I made this database in order to master (my)sql. The names are simple 
and unique as are the story titles. In fact this database is about a 
series of stories in my country known as 'Bommel-saga'. So there is no 
need for concern about more people having the same name etc.. My 
story_person table is localisation which is in my humble view a perfect 
name for what it is doing.
I thank everybody very much; I learned a lot about joins.
Onno

[toc] | [prev] | [next] | [standalone]

#5508

Onno Dreischor wrote:

> Thomas 'PointedEars' Lahn schreef op 27-6-2014 9:00:
>> [Full quote]
>
> To make some things clear:
> I made this database in order to master (my)sql. The names are simple
> and unique as are the story titles. In fact this database is about a
> series of stories in my country known as 'Bommel-saga'. So there is no
> need for concern about more people having the same name etc..

I did not voice such a concern.

> My story_person table is localisation which is in my humble view a perfect
> name for what it is doing.

Localisation (L10n) usually means the adaptation of content to the 
requirements of a specific country or region.  For example, number, 
currency, and date formats fall within the area of software localisation.

I understand that Bommelsaga is a Dutch comic strip series, in which case 
localisation could mean that the same story/character has different 
title/name for publication in different countries/languages.

For example, the druid of Astérix’s village is called « Panoramix » in the 
French original, „Miraculix“ in German(y), “Getafix” in the UK version, and 
“Magigimmix” in the US version, to name a few.  But although the name of the 
character is different in different localisations, it would still be the 
same character and in the same story of the series.  Therefore, you would 
have the following relations:

     story        story_character    character            localisation
     ________     _______________    _______________      _______________
 ,-> story_id <-- story_id       .-> character_id     .-> localisation_id <.
 :            1:n character_id --'   localisation_id -'   name             :
 :                              n:1  name            n:1               n:1 :
 : n:1                                                                     :
 :                                                                         :
 :   story_title                                                           :
 :   _______________                                                       :
 `-- story_id                                                              :
     localisation_id ------------------------------------------------------'
     title
     year

And data that looks like this:
 
  story     story_character
  ________  ______________________ 
  story_id  story_id  character_id
  1         1         4

  character                                  localisation
  _________________________________________  _____________________________
  character_id  localisation_id  name        localisation_id  name
  4             1                Panoramix   1                French
  4             2                Miraculix   2                German
  4             3                Getafix     3                English (UK)
  4             4                Magigimmix  4                English (US)

  story_title
  ____________________________________________________
  story_id  localisation_id  title                year
  1         1                Astérix le Gaulois   1961
  1         2                Asterix der Gallier  1968
  1         3                Asterix the Gaul     1969
  1         4                Asterix the Gaul     1969

(`story` could then also store story-related information that is not stored 
in one of the other tables and does not depend on localisation.)

If I had to find the stories that would feature both Idéfix/Idefix/Dogmatix 
(character_id = 3) and Panoramix/Miraculix/Getafix/Magigimmix 
(character_id = 4), the query would be:

  SELECT `story_id`
  FROM `story_character`
  WHERE `character_id` IN (3, 4)
  GROUP BY `story_id`
  HAVING (COUNT(`character_id`) = 2)

(as I suggested previously)

If I did not know the character IDs but their German names, I could write:

  SELECT `story_id`
  FROM `story_character`
  LEFT JOIN `character` AS c USING (`character_id`)
  WHERE ((c.`localisation_id` = 2)
    AND (c.`name` IN ('Idefix', 'Miraculix')))
  GROUP BY `story_id`
  HAVING (COUNT(`character_id`) = 2)  

If I only wanted to know the original (French) title of the matching 
stories, and the year of publication, along with the comma-separated 
character names, and the results ordered by year of publication, I could 
write:

  SELECT `story_id`, `title`, `year`, GROUP_CONCAT(c.`name`, ', ')
  FROM `story_character` AS sc
  LEFT JOIN `story_title` AS st USING (`story_id`)
  LEFT JOIN `character` AS c USING (`character_id`)
  WHERE ((st.`localisation_id` = 1)
    AND ((c.`localisation_id` = 2)
      AND (c.`name` IN ('Idefix', 'Miraculix')))
  GROUP BY `story_id`, `year`
  HAVING (COUNT(sc.`character_id`) = 2)

And so on.  (Untested.  Writing a query that would list the French story 
titles with German character names without knowing either ID is left as an 
exercise to the reader.)
  
> I thank everybody very much; I learned a lot about joins.

As you have pointlessly full-quoted my posting, it is also obvious
that you have not read it.  Bottom line: While you may have learned 
something about JOINs this time, you do not necessarily need them
in this case.

<http://www.netmeister.org/news/learn2quote.html>

-- 
PointedEars

Twitter: @PointedEars2
Please do not Cc: me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [next] | [standalone]

#5509

Thomas 'PointedEars' Lahn:

>Onno Dreischor wrote:
>
>> Thomas 'PointedEars' Lahn schreef op 27-6-2014 9:00:
>>> [Full quote]
>>
>> To make some things clear:
>> I made this database in order to master (my)sql. The names are
>>simple  and unique as are the story titles. In fact this database
>>is about a  series of stories in my country known as 'Bommel-saga'.
>>So there is no  need for concern about more people having the same
>>name etc..
>
>I did not voice such a concern.
>
>> My story_person table is localisation which is in my humble view a
>>perfect  name for what it is doing.

I doubt that, Onno, but since you're only using this case for practice,
it is not really relevant. You could have called the table
"collyflower" (bloemkool) and still use it for the same purpose.


>Localisation (L10n) usually means the adaptation of content to the 
>requirements of a specific country or region.  For example, number, 
>currency, and date formats fall within the area of software
>localisation.
>
>I understand that Bommelsaga is a Dutch comic strip series, in which
>case localisation could mean that the same story/character has
>different title/name for publication in different countries/languages. 

Because OP doesn't have any language- or location relevant attribute in
his Stories or Localisation tables, I think it's unlikely that he wants
to use the term 'localisation' this way. Rather, OP may well be
overestimating the degree of perfectness.

Side note: your comparison to the Asterix series is realistic, since
Marten Toonder's comic stories about "Tom Poes and Olivier B. Bommel"
have been translated to some 20 languages, and the names used in the
stories have similar characteristics.

-- 
Erick

[toc] | [prev] | [next] | [standalone]

#5512

On 6/27/2014 2:06 PM, Onno Dreischor wrote:
> Thomas 'PointedEars' Lahn schreef op 27-6-2014 9:00:
>> Onno Dreischor wrote:
>>
>>> I have made a small MySQL-database about persons figuring in a series of
>>> little stories. The structure is, given as TABLE(field1,field2):
>>> PERSON (pid, name)
>>> STORY (sid, title)
>>> LOCALISATION (lid, pid, sid).
>>> Now I'm trying to write a query giving the stories where two given
>>> persons appear. Somehow I feel that this is rather easy, but in a way
>>> I'm blind for that. As I see it the problem is in fact having two
>>> variables in the same table.
>>> Am I right and how can I tackle this problem.
>>
>> Why would a localisation that is related to the same story but different
>> person, or to the same people but a different story, be a different
>> localisation, having a different `lid`?
>>
>> You need a table that relates people with stories, e.g.
>> `story_person`.  If
>> so, its primary key should be a multi-column key containing both `sid`
>> and
>> `pid` (in order to avoid duplicate relations at least).
>>
>> [Then ISTM you need *another* table that relates a localisation to a
>> story.
>> But that has nothing to do with your question.]
>>
>> If you have `story_person`, you can use logical operators in the WHERE
>> clause, GROUP the result BY story and filter for the stories HAVING
>> exactly
>> two of people you are looking for (because the OR also gives you the
>> story
>> even if only one of them appears):
>>
>>    SELECT `sid`
>>    FROM `story_person`
>>    WHERE ((`sid` = 42) AND ((`pid` = 23) OR (`pid` = 1337)))
>>    GROUP BY `sid`
>>    HAVING (COUNT(`pid`) = 2)
>>
>> or
>>
>>    …
>>    WHERE ((`sid` = 42) AND (`pid` IN (23, 1337))
>>    …
>>
>> If you also need the story title, you would need to JOIN with the `story`
>> table to get the values for the `title` field.
>>
>> If you need the `name`s of the people in each matching story, or
>> filter by
>> them instead of their `pid`s, you would need to JOIN also with the
>> `person`
>> table, and you can use the GROUP_CONCAT() function to return the names of
>> the people as a list in one result field, for example as a
>> comma-separated
>> list.
>>
>> In this case you do not necessarily need subqueries nor need you to join
>> `story_person` with itself as the others have suggested.
>>
>> <http://dev.mysql.com/doc/>
>>
> To make some things clear:
> I made this database in order to master (my)sql. The names are simple
> and unique as are the story titles. In fact this database is about a
> series of stories in my country known as 'Bommel-saga'. So there is no
> need for concern about more people having the same name etc.. My
> story_person table is localisation which is in my humble view a perfect
> name for what it is doing.
> I thank everybody very much; I learned a lot about joins.
> Onno

Onno,

You'll be better off just ignoring "Pointed Head".  He has no idea what
you're talking about (as his posts prove).

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.mysql

csiph-web