June 4, 2009 at 12:53 pm
select Description, PointsLimit from ReferTypeLimits
Results:
Description PointsLimit
----------- ----------
Referral 700
Registered 800
I need to flatten this into one row. I tried using a CTE.
WITHReferralLimits (Description, PointsLimit) AS
(
SELECT Description, PointsLimit
FROM ReferTypeLimits
WHERE ReferTypeLimitsID = 1
),
RegistrationLimits (Description, PointsLimit) AS
(
SELECT Description, PointsLimit
FROM ReferTypeLimits
WHERE ReferTypeLimitsID = 2
)
SELECT isNull(ReferralLimits.Description,'Referral') AS ReferralDescription
, ReferralLimits.PointsLimit AS ReferralPointsLimit
, isNull(RegistrationLimits.Description,'Registered') AS RegistrationDescription
, RegistrationLimits.PointsLimit AS RegistrationPointsLimit
FROM ReferralLimits
LEFT JOIN RegistrationLimits ON ReferralLimits.Description = RegistrationLimits.Description
Results:
ReferralDescription ReferralPointsLimit RegistrationDescription RegistrationPointsLimit
----------------- ----------------- ---------------------- ---------------------
Referral 700 Registered NULL
The problem is the NULL value and not sure what to join on.
I know I could do 4 sub select queries but that is very expensive.
Can I use a CTE or is there a different way to accomplish this?
June 4, 2009 at 1:06 pm
Not sure if this is the correct way to do this but I realized that I needed some value that I could join on; so I added Temp and it works great. If anybody else has a better solution let me know.
WITHReferralLimits (Temp, Description, PointsLimit) AS
(
SELECT 1 AS Temp, Description, PointsLimit
FROM ReferTypeLimits
WHERE ReferTypeLimitsID = 1
),
RegistrationLimits (Temp, Description, PointsLimit) AS
(
SELECT 1 AS Temp, Description, PointsLimit
FROM ReferTypeLimits
WHERE ReferTypeLimitsID = 2
)
SELECT isNull(ReferralLimits.Description,'Referral') AS ReferralDescription
, ReferralLimits.PointsLimit AS ReferralPointsLimit
, isNull(RegistrationLimits.Description,'Registered') AS RegistrationDescription
, RegistrationLimits.PointsLimit AS RegistrationPointsLimit
FROM ReferralLimits
LEFT JOIN RegistrationLimits ON ReferralLimits.Temp = RegistrationLimits.Temp
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply