SQL Server – Right Outer Join Not Working Due to WHERE Clause

sql servert-sql

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?

Best Answer

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;