Help with combining 2 query and make subquery

[jtrembla]

Query 1 (I want all the results and only matching results from query 2)

They will be joined on PricingDrugGroupID and ForecastPeriodTimelineID eventhough query 2 does not return these columns it is in the table.

Query 1 code...

SELECT  dbo.PricingDrugGroup.PricingDrugGroupID,
        dbo.PricingDrug.PricingDrugName,
        dbo.PricingDrug.SequenceNumber As PricingDrugSequenceNumber,
        dbo.Geography.GeographyName,
        dbo.Geography.SequenceNumber,
        dbo.PricingDrugGroupType.PricingDrugGroupTypeID,
        dbo.PricingDrugGroupType.PricingDrugGroupTypeName,
        dbo.ForecastPeriodTimeline.PeriodTimelineDate,
         dbo.ForecastPeriodTimeline.ForecastPeriodTimelineID,
        dbo.ForecastPeriod.PeriodTypeID
FROM    dbo.ForecastPeriod
        INNER JOIN dbo.ForecastPeriodTimeline ON dbo.ForecastPeriod.ForecastPeriodID = dbo.ForecastPeriodTimeline.ForecastPeriodID,
        dbo.PricingDrugGroup
        INNER JOIN dbo.PricingDrug ON dbo.PricingDrugGroup.PricingDrugID = dbo.PricingDrug.PricingDrugID
        INNER JOIN dbo.VersionGeography ON dbo.PricingDrugGroup.VersionGeographyID = dbo.VersionGeography.VersionGeographyID
        INNER JOIN dbo.Geography ON dbo.VersionGeography.GeographyID = dbo.Geography.GeographyID
        INNER JOIN dbo.PricingDrugGroupType ON dbo.PricingDrugGroup.PricingDrugGroupTypeID = dbo.PricingDrugGroupType.PricingDrugGroupTypeID

WHERE   dbo.PricingDrug.VersionID = 1395
           AND dbo.ForecastPeriod.VersionID = 1395
           AND dbo.PricingDrugGroup.BrandGeneric = 'Brand'

query 2

SELECT AdjustmentValue
FROM dbo.ForecastAdjustment

 

[RiverGuy]

They will be joined on PricingDrugGroupID and ForecastPeriodTimelineID eventhough query 2 does not return these columns it is in the table.

There is no way to join on two columns which don't exist. Maybe what you're saying is that dbo.ForecastAdjustment contains just one record and you want to include that one value from that one record in each row of your resultset?

 

[jtrembla]

Yep, query 2 has a field called Adjustment value, the table
ForecastAdjustment has fields PricingDrugGroupId and ForecastPeriodTimelineID.

My Final results need to be

Pricingdruggroupid
ForecastPeriodtimelineid,
Pricingdrugname,
Geographyname,
PeriodTimelineDate
AdjustmentValue (from query 2)

I presume query 2 will be the outer query and query 1 will be in the From clause of query 2?

 

[RiverGuy]

But one important question needs to be answered first--does the table dbo.ForecastAdjustment have just one record, and will it always only have one record? If not, how would you decide which record to use?

 

[jtrembla]

no it can have more than one record, so my result will be like


Pricing drug groupid Geographyname adjustment value
1 usa null
2 france .58
3 uk null

This is what I came up with thus far, I am quite new at "writing t-sql" so any guidance will help.

SELECT  a.PricingDrugGroupID,
        a.PricingDrugName,
        a.GeographyName,
        a.PricingDrugGroupTypeName,
        a.PeriodTimelineDate,
        a.PeriodTypeID,
        a.PricingDrugSequenceNumber,
        a.PricingDrugGroupTypeID,
        AdjustmentValue
FROM    ( SELECT    dbo.PricingDrugGroup.PricingDrugGroupID,
                    dbo.PricingDrug.PricingDrugName,
                    dbo.PricingDrug.SequenceNumber As PricingDrugSequenceNumber,
                    dbo.Geography.GeographyName,
                    dbo.Geography.SequenceNumber,
                    dbo.PricingDrugGroupType.PricingDrugGroupTypeID,
                    dbo.PricingDrugGroupType.PricingDrugGroupTypeName,
                    dbo.ForecastPeriodTimeline.PeriodTimelineDate,
                    dbo.ForecastPeriodTimeline.ForecastPeriodTimelineID,
                    dbo.ForecastPeriod.PeriodTypeID
          FROM      dbo.ForecastPeriod
                    INNER JOIN dbo.ForecastPeriodTimeline ON dbo.ForecastPeriod.ForecastPeriodID = dbo.ForecastPeriodTimeline.ForecastPeriodID,
                    dbo.PricingDrugGroup
                    INNER JOIN dbo.PricingDrug ON dbo.PricingDrugGroup.PricingDrugID = dbo.PricingDrug.PricingDrugID
                    INNER JOIN dbo.VersionGeography ON dbo.PricingDrugGroup.VersionGeographyID = dbo.VersionGeography.VersionGeographyID
                    INNER JOIN dbo.Geography ON dbo.VersionGeography.GeographyID = dbo.Geography.GeographyID
                    INNER JOIN dbo.PricingDrugGroupType ON dbo.PricingDrugGroup.PricingDrugGroupTypeID = dbo.PricingDrugGroupType.PricingDrugGroupTypeID
          WHERE     dbo.PricingDrug.VersionID = 1395
                    AND dbo.ForecastPeriod.VersionID = 1395
                    AND dbo.PricingDrugGroup.BrandGeneric = 'Brand'
        ) AS A
        LEFT JOIN dbo.ForecastAdjustment ON a.PricingDrugGroupID = dbo.ForecastAdjustment.PricingDrugGroupID
                                            AND A.ForecastPeriodTimelineID = dbo.ForecastAdjustment.ForecastPeriodTimelineID

 

[RiverGuy]

You have the query written correctly, although it doesn't really match your first statement. I was under the impression your ForecastAdjustment table did not contain the PricingDrugGroupID or ForecastPeriodTimelineID columns.

 

[jtrembla]

it did. Any recommendations on make my query better, readable etc.? I am new to this thus want to make sure the next person who reads this won't pull his hair, lol.

 

[RiverGuy]

Readability is important, although it is a bit low on the priority list compared to performance. How does the query perform? Is it fast or slow?

 

[jtrembla]

it is fast. I am trying to be anal about indexes and normalization.

I broke one normalization rule. We have drugs that can added as an emerging drug, but they are added 3 different ways...

Emerging brand
Emerging Brand from Existing Brand
Emerging Generic from Either EmergingBrand or EmergingGeneric

when they get added, they have some attributes in common and some that are not. However, I just created one EmergingDrugInfo table (only 6 fields) instead of creating 3 seperate tables (which they all would relate back to the parent PricingDrugGroup table).