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 6th, 2015 3:52am

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).

Hi Dheeraj,

In addition to Uri's.

So the studyComponent and Series are in a one-to-many relationship. Based on the sample data, the column StudyUID and PPSID are referenced as a foreign key. Those two tables can be joined based on the referenced columns. Regarding column Modality, why it exists in both tables, it seems to violate the 2NF and 3NF. On my understanding, Modality seems redundant is there any case that Modality has different values in those tables between the rows having the same StudyUID and PPSID, expect the empty case? You might be able to drop or abandon that column in studyComponent.

Free Windows Admin Tool Kit Click here and download it now
September 7th, 2015 1:36am

Hi Uri Dimant,

Thanks for your reply!

The problem with query is, it will repeat based on the number of series under a studycomponent.

I want only a list of StudyComponent and Modality from Series.

September 7th, 2015 2:15am

Hi Eric,

StudyUID, Modality and PPSID create a unique combination to find the underlying series in Series table. These values are generated by a physical medical machine.

StudyComponent is being populated by application and because of some internal bug, get the modality empty. Now I am writing a update query so that I can update modality in StudyComponent from a bunch of Series.

Hope it will clear the situation.

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

What about below query? Can you judge any issue in it?

;with tempUSID AS
(
select [Study Instance UID], Modality, PPSID 
from Series
GROUP by [Study Instance UID], Modality, PPSID
)
select  t.Modality, sc.* from tempUSID t
JOIN StudyComponent sc ON 
(
sc.[Study Instance UID] = sc.[Study Instance UID]
AND (len(sc.Modality) = 0 OR sc.Modality = t.Modality)
AND sc.PPSID = t.PPSID
)

September 7th, 2015 2:24am

Does it return correct result? BTW, does not the query have a WHERE condition?
Free Windows Admin Tool Kit Click here and download it now
September 7th, 2015 2:36am

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

Other recent topics Other recent topics