Hi,
I have two tables
StudyComponent(PK - StudyUID, Modality, PPSID)
- StudyUID
- Modality
- PPSID
- other details
Series(PK -UniqueSeriesID)
- StudyUID
- Modality
- PPSID
- UniqueSeriesID
- other details
One StudyComponent can have multiple Series. There is a scenario where modality field in StudyComponent can be empty. In this case I need to fetch modality from Series ( N rows -need only one row).
I need your help to write the query.
Example
StudyComponent -
StudyUID -'1.1.1.2'
Modality - empty
PPSID - '1234'
Series -
Row1
StudyUID -'1.1.1.2'
Modality - 'CT'
PPSID - '1234'
UniqueSeriesID - 1
Row2
StudyUID -'1.1.1.2'
Modality - 'CT'
PPSID - '1234'
UniqueSeriesID - 2
As shown in example, there is one studycomponent and it has two series. In final result, I want one row from StudyComponent and modality (CT) which is fetched from Series table.
Thanks a lot for your patience and help.
- Moved by Lydia ZhangMicrosoft contingent staff, Moderator 22 minutes ago more appropriate