jeudi 26 février 2015

Postgres Join that allows for an empty table


I have the following schema, and I'm having a lot of trouble joining these tables properly. I'm trying to get all folders that don't have a file updated in the last 10 mins in them. But I want to include any folders that don't have any files.



create table folders {
id int
table_name string
};

create table files {
id int,
folder_id int,
file_name string,
last_updated timestamp with time zone
};


My naive attempt so far is something like the following (this is a re-creation from the CLI):


Select t.folder_name from folders t join files f on (f.folder_id = t.id) where last_updated < 15m group by t.id


I can get either all folders to show up OR the latest file for each, but not both.


Any help would be greatly appreciated





Aucun commentaire:

Enregistrer un commentaire