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

Groups > comp.databases.mysql > #5504

Re: select problem

Show all headers | View raw

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.

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