The rewritten title is: How to match two attributes and get multiple results using SQL query?

Question

Long story short, I'm working on an app that matches people who want to adopt a dog with available dogs in shelters based on size, age, and other characteristics.

My query encountered some problems when using CASE, for example, the following code is only a part of the whole code, for simplicity:

ANI.IS_HOUSE_TRAINED = CASE  
            WHEN APL.DEMANDS_HOUSE_TRAINED = '仅需训练过的' THEN '是'
            WHEN APL.DEMANDS_HOUSE_TRAINED = '训练不必须' THEN '否' 
            END

The code above basically says that if the applicant requests a "trained dog", match "trained dog", The second option for applicants is "Training Not Required" and this person should be a match for both trained and untrained dogs. But I can't seem to figure out how to do this.

I also tried creating a third CASE as shown below, but that didn't work either because the second CASE already matched:

ANI.IS_HOUSE_TRAINED = CASE  
        WHEN APL.DEMANDS_HOUSE_TRAINED = '仅需训练过的' THEN '是'
        WHEN APL.DEMANDS_HOUSE_TRAINED = '训练不必须' THEN '否'             
        WHEN APL.DEMANDS_HOUSE_TRAINED = '训练不必须' THEN '是'                     
END

Can anyone help me? How to deal with situations where there are multiple possible combinations?

Answer 1

This is my understanding of the problem:

ANI.IS_HOUSE_TRAINED = 
      CASE  
        WHEN APL.DEMANDS_HOUSE_TRAINED = '仅需训练上厕所' THEN '是'
        ELSE ANI.IS_HOUS_TRAINED
      END