0

I want to make a view from these tables. The first inner join works as expected and brings a new column with AuspraegungID from which I want to connect to the ArtikelVarianten table

At the moment I get a view with all ID that have a counterpart in ArtikelVarianten. But some articles dont have that ID (its set to zero, and no match in the target table) so they are ommitted in the current selection But I want to include them, and I thought thats where the full outer join comes in.

Select Distinct [A].[Artikelnummer],
  [A].[Bezeichnung1],
  [S].[AuspraegungID],
  [A].[USER_SkuDe] As [Parent SKU],
  [AV].[USER_SkuDe] As [Varianten SKU]
From [dbo].[KHKArtikel] [A]
  inner Join  [dbo].[LBSysStock] [S] 
    On [S].[Artikelnummer] = [A].[Artikelnummer]
  Full Outer Join [dbo].[LBSysArtikelVarianten] [AV] 
  On [S].[AuspraegungID] = [AV].[AuspraegungID] 
    And [AV].[Artikelnummer] = [A].[Artikelnummer]  

closed as unclear what you're asking by Max Vernon, ypercubeᵀᴹ, LowlyDBA, mustaccio, MDCCL Feb 9 at 22:59

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

Firstly, I'd encourage you to read the blog post Be Careful When Mixing INNER and OUTER Joins.

If you use Derived Tables (or nested joins) the way you'd use parenthesis in a math or boolean equation to express your logical order of precedence, you can make your code cleaner and more readable and you can ensure that you get back the exact results you intended.

I don't know if this will solve your problem, as you did not post any table definitions or sample data, but I'd try changing your query to encapsulate the inner join into a derived table and then join the derived table using the FULL OUTER JOIN. Here is an example (not tested):

SELECT DISTINCT [ij].[Artikelnummer]
    ,[ij].[Bezeichnung1]
    ,[ij].[AuspraegungID]
    ,[ij].[USER_SkuDe] AS [Parent SKU]
    ,[AV].[USER_SkuDe] AS [Varianten SKU]
FROM (
    SELECT [A].[Artikelnummer]
        ,[A].[Bezeichnung1]
        ,[A].[USER_SkuDe]
        ,[S].[AuspraegungID]
    FROM [dbo].[KHKArtikel] [A]
    INNER JOIN [dbo].[LBSysStock] [S]
        ON [S].[Artikelnummer] = [A].[Artikelnummer]
    ) ij --inner join
FULL OUTER JOIN [dbo].[LBSysArtikelVarianten] [AV]
    ON [ij].[AuspraegungID] = [AV].[AuspraegungID]
        AND [AV].[Artikelnummer] = [ij].[Artikelnummer]

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