How to query 3 tables in PSQL?

Question

I am new to databases, I am not sure how to query these 3 tables.

I am trying to find out the total deaths(Table COVID - TotalDeathUK) of each strain(Table Strain) for the last 1 year(Table - Time?)

Table COVID

 centerid | patientid | dateid | strainid | totaldeathsuk | 
----------+-----------+--------+----------+---------------+
   100000 |    100000 | 100000 |   100000 |         31369 |       
   100000 |    100000 | 100000 |   100000 |         95382 |       
   100004 |    100004 | 100004 |   100000 |         46031 |

----------------------------------------------------------------

Table Strain

 strainid |   name    | risk 
----------+-----------+------
   100000 | Fixflex   |   50
   100001 | Cardguard |   85
   100002 | Zaam-Dox  |   53

Table Time

 dateid | year |    month     | quater | week | dayofweek |   hour   
--------+------+--------------+--------+------+-----------+----------
 100007 | 2039 | Thin Ice     |      2 |    3 |         7 | 11:54:00
 100008 | 2109 | Parineeta    |      4 |    1 |         6 | 20:12:00
 100011 | 2096 | Crawlspace   |      4 |    2 |         7 | 19:03:00

Link to Image of the Database Schema

What do you think you meant to select when you selected [jquery](https://stackoverflow.com/tags/jquery/info)? — freedomn-m, Feb 03 '21 at 12:38
Please **[edit]** your question (by clicking on the [edit] link below it) and add some sample data and the expected output based on that data as [formatted text](https://meta.stackoverflow.com/a/251362). See [here](https://meta.stackexchange.com/questions/81852) for some tips on how to create nice looking text tables. ([edit] your question - do **not** put code or additional information in comments) — , Feb 03 '21 at 12:49
@a_horse_with_no_name I want to find out for example if theTotalDeathsUk is 31369, the Strain name for the last 1 year — G Ivanov, Feb 03 '21 at 13:26
But what is patientId, dateId ..., totaldeathUK is then? It is confusing to see totalSomething per patientId, dateId, ... Would you please show the expected output for your data. — Cetin Basoz, Feb 03 '21 at 15:11
@CetinBasoz basically this is supposed to be a Data Warehouse, the table covid is a fact table, I followed some google designs which I found, I asked my lecturer for feedback he told me there is no such a thing as wrong and right. This is supposed to support decision making — G Ivanov, Feb 03 '21 at 19:05
@GIvanov\ I am sure your lecturer didn't mean this design when he said that. Anyway, I asked for an explanation of your data and expected output. making the joins and query is the easy part but at this stage we need to understand what needs to be done. — Cetin Basoz, Feb 03 '21 at 20:26

score 0 · Answer 1 · answered Feb 04 '21 at 21:44

CREATE TABLE Covid(CenterID int,PatientID int, DateID int,StrainID int,TotalDeathUK int);
CREATE TABLE Strain(StrainID int, Name varchar(20),Risk int);
CREATE TABLE Time(DateID int,Year int,Month varchar(20),Quarter int,Week int,DayOfWeek int,Hour varchar(8))

INSERT INTO Covid(CenterID,PatientID, DateID,StrainID,TotalDeathUK)
VALUES(100000,100000,100000,100000,31369),
(100000,100000,100008,100000,95382),
(100004,100004,100011,100000,46031);
INSERT INTO Strain(StrainID, Name,Risk)
VALUES( 100000,'Fixflex ',50),( 100001,'Cardguard ',85)
,( 100002,'Zaam-Dox ',53);
INSERT INTO Time(DateID,Year,Month,Week,Quarter,DayOfWeek,Hour)VALUES(100000,2020,'Thin Ice',2,3,7,'11:54:00'),
(100008,2019,'Parineeta',4,1,6,'20:12:00'),
(100011,2018,'Crawlspace',4,2,7,'19:03:00');

SELECT T.Year,S.StrainID,C.TotalDeathUK FROM Covid AS C
LEFT JOIN Strain AS S ON S.StrainID=C.StrainID
LEFT JOIN Time AS T ON T.DateID=C.DateID
WHERE T.Year=date_part('year', (SELECT current_timestamp))-1

year | strainid | totaldeathuk
---: | -------: | -----------:
2020 |   100000 |        31369

db<>fiddle here

How to query 3 tables in PSQL?

1 Answers1