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

Groups > comp.databases.mysql > #5497

Re: select problem

Show all headers | View raw

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

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