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

Groups > comp.databases.mysql > #5512

Re: select problem

Show all headers | View raw

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

Back to comp.databases.mysql | Previous | Next — Previous 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