Case(), when there are muliple related records - looking beyond the first record

[fmphysio]

I'm a little stuck here. I'm trying to get a calculation to display a field from a particular related record, based on another field's content.

UR_NUMBERS(3 fields)

ur_id ur_hospital : ur_number

The ur_id = patient_id.

What the table could look like with one patient, but three hospitals and ur numbers:

1 : Hospital A : 123456

1 : Hospital B : abcdef

1 : Hostpial C : 654cba

I want to create a calculation, for where ur_hospital is Hospital A, display the corresponding ur_number (123456).

So far I have:

Case ( ur_numbers::ur_hospital = "Hospital A"; ur_numbers::ur_number)

However, this only works if Hospital A happens to have been created first. If I was to change this Case to "Hospital B", I get a blank result.

How can I make it look through all the records, instead of just going to the first related record only?

[comment]

Define another relationship (using a second occurrence of the UR NUMBERS table), matching on both patient_id AND the hospital.

[fmphysio]

Ahhh of course! Thank-you so much!