SQL Join on value a otherwise value b

Question

I have two tables which I want to join in a certain way.

Table Resource:

id	type
1	1234
2	2345

Table Options:

articleId	typeId	optionKey
1	1234	1
1	2345	5
2	2345	5

What I basically want to achieve is getting all articleId's with optionKey 1. If optionKey 1 doesn't exist it should look for optionKey 5. There may also be situations that neither 1 or 5 exist, resulting in the following desired table:

articleId	typeId	optionKey
1	1234	1
2	2345	5

I'm already using a very specific query in an SQL view for our SAP system. It does work in a way, the only thing is that it still returns articleId's for both 1 and 5 if they both exist.

select
        resource."articleId",
        COALESCE(imageTypeChoice1."optionKey",imageTypeChoice5."optionKey") as "imageTypeOptionId"
from "resource" as resource
left join "Options" as imageTypeChoice1 on resource."type" = imageTypeChoice1."id" AND imageTypeChoice1."optionKey" = '1'
left join "Options" as imageTypeChoice5 on resource."type" = imageTypeChoice5."id" AND imageTypeChoice5."optionKey" = '5'
where (imageTypeChoice1."optionKey" IS NOT NULL OR imageTypeChoice5."optionKey" IS NOT NULL)

Preferably we would like to do this without subquery's.

Answer 1

I think what you need, given just the sample data provided, is to aggregate your options before joining.

select r.Id articleId, r.type, o.optionKey
from resource r
join (
    select articleid,  Min(optionkey) optionkey
    from options
    group by articleid
)o on o.articleId = r.id