Komplizierte Abfrage (Artikel an mehreren Standorten)

Question

Ich habe - stark abstrahiert - eine Tabelle mit Standort (long), Artikel (long) und Bezeichnung (varchar).

Ein Artikel kann an mehreren Standorten sein, ist aber immer im Hauptsitz (00) vorhanden. Die "Bezeichnung" kann pro Standort variieren

Beispiel:

Standort   Artikel     Bezeichnung
0        1      Artikel 1
0        2      Artikel 2
0        3      Artikel 3
1        2      Artikel 2a
1        3      Artikel 3a
2        2      Artikel 2b

Skript zur Erstellung:

CREATE TABLE [dbo].[Test](
	[Ort] [int] NOT NULL,
	[Art] [int] NOT NULL,
	[Bez] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
	[Ort] ASC,
	[Art] ASC
) 
) 

INSERT INTO Test SELECT 0, 1, 'Artikel 1'
INSERT INTO Test SELECT 0, 2, 'Artikel 2'
INSERT INTO Test SELECT 0, 3, 'Artikel 3'

INSERT INTO Test SELECT 1, 2, 'Artikel 2a'
INSERT INTO Test SELECT 1, 3, 'Artikel 3a'
INSERT INTO Test SELECT 2, 1, 'Artikel 1b'
INSERT INTO Test SELECT 2, 2, 'Artikel 2b'

 

Ich muss nun Abfrage erstellen, welche mir für einen bestimmten Standort (z.B. 1) die dort vorhandenen Artikel mit der dortigen Bezeichnung UND - wenn der Artikel dort nicht vorhanden ist - den Artikel + Bezeichnung aus dem Standort 0 anzeigt.

Per LEFT / RIGHT JOIN bekomme ich das nicht hin (da fehlt mir immer der Artikel aus dem Hauptstandort), UNION, Gruppierung etc. habe ich auch nicht hinbekommen.

SELECT * FROM Test as T
 LEFT JOIN Test as T0
  on t0.Ort = 0
  AND T0.Art = T.Art 
 WHERE T.Ort = 1

-- Ist-Ergebnis (Artikel 1 fehlt):
-- O A   Bez      Ort  Art   Bez2
-- 1	2	Artikel 2a	0 	 2	Artikel 2
-- 1	3	Artikel 3a	0	 3	Artikel 3

-- Soll-Ergebnis:
-- Ort  Artikel  Bezeichnung
-- 0    1     Artikel 1
-- 1    2     Artikel 2a
-- 1    3     Artikel 3a

Answer 1

hi,

eigentlich nicht so schwer:

DECLARE @Test TABLE
    (
      [Ort] [int] NOT NULL ,
      [Art] [int] NOT NULL ,
      [Bez] [varchar](50) NOT NULL
    ) ;

INSERT  INTO @Test
VALUES  ( 0, 1, 'Artikel 1' ),
        ( 0, 2, 'Artikel 2' ),
        ( 0, 3, 'Artikel 3' ),
        ( 1, 2, 'Artikel 2a' ),
        ( 1, 3, 'Artikel 3a' ),
        ( 2, 1, 'Artikel 1b' ),
        ( 2, 2, 'Artikel 2b' ) ;

SELECT  *
FROM    @Test ;

WITH    Artikelstamm
          AS ( SELECT   *
               FROM     @Test
               WHERE    [Ort] = 0
             )
    SELECT  CASE WHEN T.[Ort] IS NULL THEN A.[Ort]
                 ELSE T.[Ort]
            END AS [Ort] ,
            CASE WHEN T.[Art] IS NULL THEN A.[Art]
                 ELSE T.[Art]
            END AS [Art] ,
            CASE WHEN T.[Bez] IS NULL THEN A.[Bez]
                 ELSE T.[Bez]
            END AS [Bez]
    FROM    Artikelstamm A
            LEFT JOIN @Test T ON T.[Art] = A.[Art]
                                 AND T.[Ort] = 1 ;

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 2

Mit ISNULL() ist's besser zu lesen:

DECLARE @Test TABLE
    (
      [Ort] [int] NOT NULL ,
      [Art] [int] NOT NULL ,
      [Bez] [varchar](50) NOT NULL
    ) ;

INSERT  INTO @Test
VALUES  ( 0, 1, 'Artikel 1' ),
        ( 0, 2, 'Artikel 2' ),
        ( 0, 3, 'Artikel 3' ),
        ( 1, 2, 'Artikel 2a' ),
        ( 1, 3, 'Artikel 3a' ),
        ( 2, 1, 'Artikel 1b' ),
        ( 2, 2, 'Artikel 2b' ) ;

SELECT  *
FROM    @Test ;

WITH    Artikelstamm
          AS ( SELECT   *
               FROM     @Test
               WHERE    [Ort] = 0
             )
    SELECT  ISNULL(T.[Ort], A.[Ort]) AS [Ort] ,
            ISNULL(T.[Art], A.[Art]) AS [Art] ,
            ISNULL(T.[Bez], A.[Bez]) AS [Bez]
    FROM    Artikelstamm A
            LEFT JOIN @Test T ON T.[Art] = A.[Art]
                                 AND T.[Ort] = 1 ;

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 3

Vielen Dank, ich habe inzwischen auch noch selber eine andere Variante gefunden (musste in meinem ursprünglichen Statement nur die beiden Standorte austauschen):

SELECT ISNULL(T.Ort, T0.Ort), ISNULL(T.Art, T0.Art), ISNULL(T.Bez, T0.Bez)
 FROM Test as T0
 LEFT JOIN Test as T
  on t.Ort = 1
  AND T0.Art = T.Art 
 WHERE T0.Ort = 0
 

Answer 4

hi,

geht auch. Die Abfrägepläne sind mit oder ohne Index (dein Primärschlüssel) identisch.

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann