CODEWITHSUNDEEP
sqlpostgresql
user3830784
user3830784

Reputation: 355

How to select elements from a table referenced by another table?

I am using postgresql for storing and analyzing data for a research project. I have a set of tables which look approximately like the following:

fileType (name varchar(80), id serial primary key);
fileTable (...various details, fileType integer references fileType(id), id serial primary key);
parameterTable (... various params, id serial primary key);
resultsTable (fileKey integer references fileTable(id), parameterKey integer references parameterTable(id), ...results);

What I want to do is write a query that looks something like the following:

SELECT COUNT(... details) FROM resultsTable WHERE parameterKey = ? AND fileKey...

And I am looking to figure out a way to get the results from all points where the fileKey references a fileTable with a specific fileType reference.

So as an example, if I have 2 file types (fileTypeA and fileTypeB), and a set of files that are of type A or B, I want to get details from the results for all fileTypes that reference fileTypeA. How would I go about writing a query that does this?

Upvotes: 0

Views: 60

Answers (1)

jpw
jpw

Reputation: 44891

Use the join keyword to connect the tables using the primary and foreign keys.

Based on your description it should probably be:

select * 
from resultsTable r
join parameterTable p on r.parameterKey = p.id
join fileTable f on r.fileKey = f.id
join fileType ft on ft.id = f.fileType
where ft.type = 'fileTypeA'

This should get you all details from the results for all fileTypes that reference fileTypeA.

Upvotes: 1

Related Questions