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

Groups > comp.databases.mysql > #5499

Re: select problem

Show all headers | View raw

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

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