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:
Where you have no match in
[src_boss_entwicklung].[dbo].[tckopf]
the columncommission.referenzid
will be NULL, meaning this condition:will not be true for these rows (because
NULL = 68
evaluates toNULL
). This effectively makes yourRIGHT OUTER JOIN
anINNER JOIN
I have never been a big fan of
RIGHT JOIN
especially whenLEFT JOIN
appears in the same query. I would rearrange the query to make all the outer joins left outer joins for continuity purposes:This does not solve the problem though since you still have the possibility of
commission.referenzid
beingNULL
and turning the LEFT OUTER JOIN into an INNER JOIN. You can get around this by moving the predicates to the JOIN conditiion:I am also fairly certain this condition can be simplified to:
Making your final query:
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
: