0

I have the following query, I need to modify it, so that it gives me the result for originDocForCurrency.id = 68 if there is no matching for commission.referenzid = 68. I tried the RIGHT OUTER JOIN ON commission.referenzid = originDocForCurrency.id, but it don't give results, if no record is found with commission.referenzid = 68.

SELECT
    commission.netto AS commissionNetValue,
    commission.r_art AS commissionDocClass,
    commission.waehrung AS commissionCurrency,
    currencies.usd_brief AS foreignCurrencyUSDAsk,
    currencies.usd_geld AS foreignCurrencyUSDBid,
    originDocForCurrency.r_art AS originDocForCurrencyDocClass,
    originDocForCurrency.poswert AS originDocForCurrencyCommission,
 (SELECT originDoc.poswert FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocCommission,
 (SELECT originDoc.r_art FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocClass,
 (SELECT originDoc.waehrung FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocCurrency
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
RIGHT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
    ON commission.referenzid = originDocForCurrency.id
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[devisen] AS currencies
    ON currencies.datum = (
            SELECT TOP 1 fc.datum
            FROM [src_boss_entwicklung].[dbo].[devisen] AS fc
            WHERE fc.datum <= originDocForCurrency.von
                AND fc.usd_brief IS NOT NULL AND fc.usd_brief > 0
                AND fc.usd_geld IS NOT NULL AND fc.usd_geld > 0
            ORDER BY fc.datum DESC
            )
WHERE
    originDocForCurrency.id = 68
    AND     
    ( commission.referenzid = 68
        AND commission.btyp = 7
        AND ( commission.storno <> 1 OR commission.storno IS NULL )
        AND ( commission.proforma <> 1 OR commission.proforma IS NULL ) )
    OR
    ( commission.referenzid = 68
        AND commission.btyp = 8
        AND ( commission.storno <> 1 OR commission.storno IS NULL )
        AND ( commission.proforma <> 1 OR commission.proforma IS NULL ) )

How can I modify the query, to get the desired result? Can I achieve the desired result maybe with a CASE in the WHERE clause?

Improve this question
4
  • You're explictly restricting yourself in the WHERE clause to commission.referenzid = 68, so that's going to cause issues whatever you do in the JOIN.
    – Mark Sinkinson
    Commented Jun 6, 2014 at 9:34
  • @MarkSinkinson yes, I know.
    – BuZZ-dEE
    Commented Jun 6, 2014 at 9:48
  • 2
    RIGHT JOIN then LEFT JOIN? I've been writing T-SQL since ~1997 and that construct still makes my head hurt. Try to put the required tables first, then LEFT JOIN to everything else. It makes the logic easier to follow for anyone else looking at the code. IMHO.
    – Aaron Bertrand
    Commented Jun 6, 2014 at 13:32
  • +1,000,000 Aaron. That and having field names that are ALL CAPS drives me crazy. Why hurt yourself like that!
    – Hannah Vernon ♦
    Commented Jun 6, 2014 at 14:01

1 Answer 1

Reset to default
5

To simplify your query slightly you have basically the following:

SELECT *
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
RIGHT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
    ON commission.referenzid = originDocForCurrency.id
WHERE
    originDocForCurrency.id = 68
AND (commission.referenzid = 68 OR commission.referenzid = 68);

Where you have no match in [src_boss_entwicklung].[dbo].[tckopf] the column commission.referenzid will be NULL, meaning this condition:

AND (commission.referenzid = 68 OR commission.referenzid = 68);

will not be true for these rows (because NULL = 68 evaluates to NULL). This effectively makes your RIGHT OUTER JOIN an INNER JOIN

I have never been a big fan of RIGHT JOIN especially when LEFT JOIN appears in the same query. I would rearrange the query to make all the outer joins left outer joins for continuity purposes:

SELECT *
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[devisen] AS currencies
    ON currencies.datum = (
            SELECT TOP 1 fc.datum
            FROM [src_boss_entwicklung].[dbo].[devisen] AS fc
            WHERE fc.datum <= originDocForCurrency.von
                AND fc.usd_brief IS NOT NULL AND fc.usd_brief > 0
                AND fc.usd_geld IS NOT NULL AND fc.usd_geld > 0
            ORDER BY fc.datum DESC
            )
WHERE
    originDocForCurrency.id = 68
AND (commission.referenzid = 68 OR commission.referenzid = 68);

This does not solve the problem though since you still have the possibility of commission.referenzid being NULL and turning the LEFT OUTER JOIN into an INNER JOIN. You can get around this by moving the predicates to the JOIN conditiion:

SELECT *
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
    AND     
    ( commission.referenzid = 68
        AND commission.btyp = 7
        AND ( commission.storno <> 1 OR commission.storno IS NULL )
        AND ( commission.proforma <> 1 OR commission.proforma IS NULL ) )
    OR
    ( commission.referenzid = 68
        AND commission.btyp = 8
        AND ( commission.storno <> 1 OR commission.storno IS NULL )
        AND ( commission.proforma <> 1 OR commission.proforma IS NULL ) 
    )

I am also fairly certain this condition can be simplified to:

SELECT *
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
    AND commission.referenzid = 68
    AND commission.btyp in (7, 8)
    AND ISNULL(commission.storno, 0) <> 1
    AND ISNULL(commission.proforma, 0) <> 1

Making your final query:

SELECT
    commission.netto AS commissionNetValue,
    commission.r_art AS commissionDocClass,
    commission.waehrung AS commissionCurrency,
    currencies.usd_brief AS foreignCurrencyUSDAsk,
    currencies.usd_geld AS foreignCurrencyUSDBid,
    originDocForCurrency.r_art AS originDocForCurrencyDocClass,
    originDocForCurrency.poswert AS originDocForCurrencyCommission,
 (SELECT originDoc.poswert FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocCommission,
 (SELECT originDoc.r_art FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocClass,
 (SELECT originDoc.waehrung FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocCurrency
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
    AND commission.referenzid = 68
    AND commission.btyp in (7, 8)
    AND ISNULL(commission.storno, 0) <> 1
    AND ISNULL(commission.proforma, 0) <> 1
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[devisen] AS currencies
    ON currencies.datum = (
            SELECT TOP 1 fc.datum
            FROM [src_boss_entwicklung].[dbo].[devisen] AS fc
            WHERE fc.datum <= originDocForCurrency.von
                AND fc.usd_brief IS NOT NULL AND fc.usd_brief > 0
                AND fc.usd_geld IS NOT NULL AND fc.usd_geld > 0
            ORDER BY fc.datum DESC
            )
WHERE
    originDocForCurrency.id = 68;

ADDENDUM

As pointed out in a comment, you don't need the three subselects, you can just use the columns in the table aliased originDocForCurrency:

SELECT
    commission.netto AS commissionNetValue,
    commission.r_art AS commissionDocClass,
    commission.waehrung AS commissionCurrency,
    currencies.usd_brief AS foreignCurrencyUSDAsk,
    currencies.usd_geld AS foreignCurrencyUSDBid,
    originDocForCurrency.r_art AS originDocForCurrencyDocClass,
    originDocForCurrency.poswert AS originDocForCurrencyCommission,
    originDocForCurrency.poswert AS originDocCommission,
    originDocForCurrency.r_art AS originDocClass,
    originDocForCurrency.waehrung AS originDocCurrency
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
    AND commission.btyp in (7, 8)
    AND ISNULL(commission.storno, 0) <> 1
    AND ISNULL(commission.proforma, 0) <> 1
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[devisen] AS currencies
    ON currencies.datum = (
            SELECT TOP 1 fc.datum
            FROM [src_boss_entwicklung].[dbo].[devisen] AS fc
            WHERE fc.datum <= originDocForCurrency.von
                AND fc.usd_brief IS NOT NULL AND fc.usd_brief > 0
                AND fc.usd_geld IS NOT NULL AND fc.usd_geld > 0
            ORDER BY fc.datum DESC
            )
WHERE
    originDocForCurrency.id = 68;
Improve this answer
1
  • 2
    The three subselects in the SELECT list can probably be removed as well - and replaced with simple columns.
    – ypercubeᵀᴹ
    Commented Jun 6, 2014 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.