Simon... Yes I forgot the "where sn.nm='std1';" restriction and...
also see you've used 2 inner joins. Thank you very much for your thoroughness. It's very much appreciated. > > On 06 April 2016 at 12:41 Simon Davies <simon.james.davies at gmail.com> > wrote: > > > On 6 April 2016 at 12:22, e-mail mgbg25171 <mgbg25171 at blueyonder.co.uk> > wrote: > > Here are my tables specified as... tbl_nm | col1, col2... > > std_nms | id, nm > > raw_nms | id, nm > > nm_pairs | id, std_nms_id, raw_nms_id > > > > I'm wondering how to supply a single std_nms.nm and get back a list of > > pairs > > i.e. std_nm.nm, raw_nms.nm.... > > that reflect each record in nm_pairs with a std_nms_id = std_nms.id > > SQLite version 3.8.11.1 2015-07-29 20:00:57 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> > sqlite> create table std_nms( id integer, nm text ); > sqlite> create table raw_nms( id integer, nm text ); > sqlite> > sqlite> create table nm_pairs( id integer, std_nms_id integer, > raw_nms_id integer ); > sqlite> > sqlite> insert into std_nms( id, nm ) values( 1, 'std1' ),( 2, 'std2' > ),( 3, 'std3' ); > sqlite> insert into raw_nms( id, nm ) values( 1, 'raw1' ),( 2, 'raw2' > ),( 3, 'raw3' ); > sqlite> > sqlite> insert into nm_pairs( id, std_nms_id, raw_nms_id ) values( 1, > 1, 1 ),( 2, 2, 2 ),( 3, 3, 3 ),( 4, 1, 3 ); > > > sqlite> select sn.nm, rn.nm > from std_nms sn > inner join nm_pairs nmp on nmp.std_nms_id=sn.id > inner join raw_nms rn on nmp.raw_nms_id=rn.id > where sn.nm='std1'; > std1|raw1 > std1|raw3 > > Regards, > Simon > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
