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
