Postgres how can I select just one row from a One -to-Many relationship between keys

user_13058607 0
Viewed 308

I have the following query

SELECT dfile.id
FROM dcmstudy_t dstud
INNER JOIN dcmseries_t dser ON dstud.id = dser.dcmstudy_id
INNER JOIN dcmfile_t dfile ON dser.id = dfile.dcmseries_id
INNER JOIN finst_t fi ON dfile.id = fi.file_id
INNER JOIN store s ON s.id = fi.store_id
WHERE dser.id in (69823713)

when given the ID number in the WHERE clause, this query will return ~300 rows of dfile.id. The problem I have is I just need one dfile.id for each dser.id in the WHERE clause. I know if I was just providing one dser.id then I could just LIMIT 1, but I need to provide many dser.id in the WHERE clause and have each produce just one dfile.id. In the end I want to be able to produce a table that contains many different dser.id with each corresponding to one dfile.id. It is probably good to mention the relationship between dser.id and dfile.id is one to many, many dfile.id to one single dser.id. Any suggestions?

UPDATE

After looking over all your suggestions I came up with the following query that solved my problem.

SELECT dser.id,s.oname,Min(dfile.id) as FileID
FROM dcmstudy_t dstud
INNER JOIN dcmseries_t dser ON dstud.id = dser.dcmstudy_id
INNER JOIN dcmfile_t dfile ON dser.id = dfile.dcmseries_id
INNER JOIN finst_t fi ON dfile.id = fi.file_id
INNER JOIN store s ON s.id = fi.store_id
WHERE dser.id in (69823713,69644830,63763440)
group by dser.id,s.oname

Thanks everyone for the input.

postgresql sql database
1 Answers

Considering that the query mentioned in the question is correct, You can use distinct on clause of PostgreSQL as mentioned below:

Try this :

SELECT distinct on (dser.id)  dfile.id 
FROM dcmstudy_t dstud
INNER JOIN dcmseries_t dser ON dstud.id = dser.dcmstudy_id
INNER JOIN dcmfile_t dfile ON dser.id = dfile.dcmseries_id
INNER JOIN finst_t fi ON dfile.id = fi.file_id
INNER JOIN store s ON s.id = fi.store_id
WHERE dser.id in (69823713) 
order by dser.id, dfile.id

above query will return the first dfile.id.

user_77724830
Related