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

Groups > comp.databases.mysql > #5495

Re: select problem

Show all headers | View raw

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

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