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.
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.