Need to write the query when there is one-to-many relationship between two tables

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.

September 5th, 2015 11:53pm

SELECT *,COALESCE(SC.Modality,S.Modality) Modality

FROM 

StudyComponent SC LEFT JOIN Series S ON SC.StudyUID=S.StudyUID

Free Windows Admin Tool Kit Click here and download it now
September 6th, 2015 2:46am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics