Sql query for multiple tables of SQLite

Question

I have an SQL database (pq) with 3 tables as sample is shown in image. I am trying to achieve below things:

• Select only rows with variable P from the MEASUREMENT column in Table 3. I tried below query but it didnt produced the correct output.

  select distinct pq_data.READ_TIME,OBS_METER,MEASUREMENT,VALUE from pq_data ORDER BY MEASUREMENT;

• Then, fetch the data columns CUST_CLASS, and SOLAR from Table 1 into Table 3 according to OBS_METER id. The OBS_METER column is not available in Table 1 but the it can be obtained from OBS_LOCATION in Table 2.

• The expected output of SQL query is Table 3 with additional columns from other tables,such as:

row id        READ_TIME          OBS_METER  OBS_LOCATION   MEASUREMENT    VALUE   CUST_CLASS  SOLAR
28/01/2018 2018/01/28 01:55:00    105714         6787            P         284         R       F

..........

I searched for existing answers: 1 , 2 but I couldnt able to write a SQL query which will produce above expected output.

Answer 1

Select only rows with variable P from the MEASUREMENT column in Table 3.

select * from pq_data WHERE MEASUREMENT='P';

Then, fetch the data columns CUST_CLASS, and SOLAR from Table 1 into Table 3 according to OBS_METER id.

select * 
from pq_data pd
inner join meter_mapping mm on pd pd.obs_meter=mm.obs_meter
inner join location_mapping lm on mm.obs_location=lm.obs_location
WHERE pd.MEASUREMENT='P'

The expected output of SQL query is Table 3 with additional columns from other tables:

You did not specify which table is the rowid that you wanted, I assumed that it was from pq_data.

Also, I don't know if an entry on pq_data will always have a match in meter_mapping (and location_maping). If it don't you need to use "left join" (or right).

It would be easier if you used the actual name of the tables in your questions (instead of table 1, 2 and 3).

select pd.rowid, pd.READ_TIME, pd.OBS_METER, mm.OBS_LOCATION, pd.MEASUREMENT, pd.VALUE, lm.CUST_CLASS, lm.SOLAR
from pq_data pd
inner join meter_mapping mm on pd pd.OBS_METER=mm.OBS_METER
inner join location_mapping lm on mm.OBS_LOCATION=lm.OBS_LOCATION
WHERE pd.MEASUREMENT='P'