How to use relationships to extract data via select

Question

Disclaimer: Novice in SQL

I have 3 tables. There is one "major" table (labeled used_for_lookup below) used to do look-ups on and the other two (labeled fid_table and did_table below) are foreign keys in the major table. For the sake of the argument, the tables below have been simplified down to the problem.

The database operations insert into the did_table and fid_table. These are generally very infrequent. When an event occurs, the details are captured in the used_for_lookup table. This table is then used to do SELECTS on. The columns fid and did in the used_for_lookup table are references into the fid_table and did_table.

A simplified version with data:

sqlite> select * from used_for_lookup;
id datetime fid did
---------- ---------- ---------- ----------
1 2014年10月10日 1 1
sqlite> select * from did_table;
id other_ints did_important_string
---------- ---------- -----------------------------------------------------
1 1 this needs to go into the query result from did table
sqlite> select * from fid_table;
id other_ints fid_important_string
---------- ---------- -----------------------------------------------------
1 1 this needs to go into the query result from fid table

The most general query is that we query used_for_lookup table between 2 date events on the datatime column.

When we do a select, like so:

sqlite> select * from used_for_lookup where datetime between '2014-10-09' and '2014-10-11';
id datetime fid did
---------- ---------- ---------- ----------
1 2014年10月10日 1 1

we need to "convert" fid and did column values to the fid_important_string and did_important_string values for when we report back the answer.

They are foreign keys which helps with data integrity, but I'm stumped on how do you now use the column lookups to get the strings out of other tables into one answer?

Thanks.

Answer 1

Try this

select used_for_lookup.id,
 used_for_lookup.datetime,
 fid_table.fid_important_string,
 did_table.did_important_string
 from used_for_lookup 
 join fid_table on fid_table.id = used_for_lookup.fid
 join did_table on did_table.id = used_for_lookup.did
where datetime between '2014-10-09' and '2014-10-11';

• user626201: Thanks - that works 99% correctly. For some reason on SQLite it need to add left join rather than just join otherwise 0 rows are returned. left join outputs the expected version so accepting the answer.

Answer 2

The following SQL performs the join you need.

select 
 used_for_lookp.id
 ,used_for_lookup.datetime
 ,fid_table.fid_important_string
 ,did_table.did_important_string
from used_for_lookup
join fid_table on fid_table.id = used_for_lookup.fid
join did_table on did_table.id = used_for_lookup.did

• CL.: datetime is not an SQLite keyword.