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

Groups > comp.databases.mysql > #5508

Re: select problem

Show all headers | View raw

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.

Back to comp.databases.mysql | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web