dotNetHell.it - Forum - Select particolare su due tabelle

autosblindo Profilo | Newbie

18 messaggi | Data Invio: mer 10 feb 2010 - 20:03

Ciao,

scusate per il titolo ma non mi veniva in mente altro di meglio.
Ho queste due tabelle

CREATE TABLE [dbo].[SAN_CRT_EXTRARIGHEDOC](
  [IdTesta] [decimal](10, 0) NOT NULL,
  [IdRiga] [int] NOT NULL,
  [Posizione] [int] NOT NULL,
  [Colata] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [Lotto] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [Protocollo] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_SAN_CRT_EXTRARIGHEDOC_1] PRIMARY KEY CLUSTERED 
(
  [IdTesta] ASC,
  [IdRiga] ASC,
  [Posizione] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

e

CREATE TABLE [dbo].[SAN_CRT_DOCUMENTI](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [SessionId] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [IdTesta] [decimal](10, 0) NOT NULL,
  [IdRiga] [int] NOT NULL,
  [Posizione] [int] NOT NULL,
 CONSTRAINT [PK_SAN_CRT_DOCUMENTI] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Inserisco alcuni dati di esempio:

INSERT INTO SAN_CRT_EXTRARIGHEDOC (IdTesta, IdRiga, Posizione, Colata, Lotto, Protocollo)
VALUES (72495,  3,  3,  '514160',  'T09120604',  '005396')
VALUES (72495,  4,  -1,  '0585247',  'T09100749',  '005400')
VALUES (72495,  4,  4,  '0585247',  'T09100748',  '005398')
VALUES (72495,  5,  -1,  '0585406',  'T10010700',  '005404')
VALUES (72495,  5,  5,  '0585406',  'T10010672',  '005402')
VALUES (72495,  6,  6,  '0585294',  'T09120590',  '005406')
VALUES (72495,  7,  7,  '0586311',  'T09110224',  '005391')
VALUES (72495,  11,  -1,  '512797',  'T09091739',  '005399')
VALUES (72495,  11,  11,  '512797',  'T09091730',  '005397')
VALUES (72495,  12,  -1,  '513008',  'T09100106',  '005403')
VALUES (72495,  12,  12,  '513008',  'T09100104',  '005401')
VALUES (72495,  15,  -1,  '841139',  'T09110045',  '005388')
VALUES (72495,  15,  15,  '841139',    ' ',    '005381')
VALUES (72495,  18,  -1,  '0418369',  'T09120471',  '005407')
VALUES (72495,  27,  -1,  '936507',  'T09112086',  '005423')
VALUES (72495,  27,  27,  '0585120',  'T09112118',  '005409')
VALUES (72495,  30,  30,  '74804',  'T09061034',  '005413')
VALUES (72495,  31,  31,  '512817',  'T09091377',  '005417')
VALUES (72495,  39,  38,  '0416451',  'T09061259',  '005424')
VALUES (72495,  40,  -1,  '512102',  'T09071713',  '005415')
VALUES (72495,  40,  39,  '512102',  'T09071705',  '005411')
VALUES (72495,  53,  -1,  '509775',  'T09031367',  '005386')
VALUES (72495,  53,  52,  '509775',  'T09031358',  '005384')

INSERT INTO SAN_CRT_DOCUMENTI (SessionId, IdTesta, IdRiga, Posizione)
VALUES ('355332917',  72495, 3, 3)
VALUES ('355332917',  72495, 4, 4)
VALUES ('355332917',  72495, 5, 5)
VALUES ('355332917',  72495, 6, 6)
VALUES ('355332917',  72495, 7, 7)
VALUES ('355332917',  72495, 10, 10)
VALUES ('355332917',  72495, 11, 11)
VALUES ('355332917',  72495, 12, 12)
VALUES ('355332917',  72495, 15, 15)
VALUES ('355332917',  72495, 18, 18)
VALUES ('355332917',  72495, 19, 19)
VALUES ('355332917',  72495, 20, 20)
VALUES ('355332917',  72495, 21, 21)
VALUES ('355332917',  72495, 22, 22)
VALUES ('355332917',  72495, 23, 23)
VALUES ('355332917',  72495, 26, 26)
VALUES ('355332917',  72495, 27, 27)
VALUES ('355332917',  72495, 30, 30)
VALUES ('355332917',  72495, 31, 31)
VALUES ('355332917',  72495, 32, 32)
VALUES ('355332917',  72495, 33, 33)
VALUES ('355332917',  72495, 37, 36)
VALUES ('355332917',  72495, 38, 37)
VALUES ('355332917',  72495, 39, 38)
VALUES ('355332917',  72495, 40, 39)
VALUES ('355332917',  72495, 41, 40)
VALUES ('355332917',  72495, 44, 43)
VALUES ('355332917',  72495, 45, 44)
VALUES ('355332917',  72495, 46, 45)
VALUES ('355332917',  72495, 47, 46)
VALUES ('355332917',  72495, 50, 49)
VALUES ('355332917',  72495, 53, 52)
VALUES ('399422893', 72495, 4, -2)
VALUES ('399422893', 72495, 4, -3)
VALUES ('399422893', 72495, 4, -4)
VALUES ('399422893', 72495, 18, -2)
VALUES ('399422893', 72495, 19, -1)

Come risultato voglio ottenere tutti i record presenti in SAN_CRT_DOCUMENTI con i campi colata, lotto e protocollo della tabella SAN_CRT_EXTRARIGHEDOC, legando i campi IdTesta, IdRiga, IdPosizione delle due tabelle, e i record presenti in SAN_CRT_EXTRARIGHEDOC che hanno stesso IdTesta e IdRiga di quelli presenti in SAN_CRT_DOCUMENTI ma posizioni diverse.
Risultato voluto con i dati di esempio riportati:
IdTesta IdRiga Posizione Colata Lotto Protocollo
--------------------------------------- ----------- ----------- -------------------------------------------------- -------------------------------------------------- ----------
72495 3 3 514160 T09120604 005396
72495 4 -4 NULL NULL NULL
72495 4 -3 NULL NULL NULL
72495 4 -2 NULL NULL NULL
72495 4 -1 0585247 T09100749 005400
72495 4 4 0585247 T09100748 005398
72495 5 -1 0585406 T10010700 005404
72495 5 5 0585406 T10010672 005402
72495 6 6 0585294 T09120590 005406
72495 7 7 0586311 T09110224 005391
72495 10 10 NULL NULL NULL
72495 11 -1 512797 T09091739 005399
72495 11 11 512797 T09091730 005397
72495 12 -1 513008 T09100106 005403
72495 12 12 513008 T09100104 005401
72495 15 -1 841139 T09110045 005388
72495 15 15 841139 005381
72495 18 -2 NULL NULL NULL
72495 18 -1 0418369 T09120471 005407
72495 18 18 NULL NULL NULL
72495 19 -1 NULL NULL NULL
72495 19 19 NULL NULL NULL
72495 20 20 NULL NULL NULL
72495 21 21 NULL NULL NULL
72495 22 22 NULL NULL NULL
72495 23 23 NULL NULL NULL
72495 26 26 NULL NULL NULL
72495 27 -1 936507 T09112086 005423
72495 27 27 0585120 T09112118 005409
72495 30 30 74804 T09061034 005413
72495 31 31 512817 T09091377 005417
72495 32 32 NULL NULL NULL
72495 33 33 NULL NULL NULL
72495 37 36 NULL NULL NULL
72495 38 37 NULL NULL NULL
72495 39 38 0416451 T09061259 005424
72495 40 -1 512102 T09071713 005415
72495 40 39 512102 T09071705 005411
72495 41 40 NULL NULL NULL
72495 44 43 NULL NULL NULL
72495 45 44 NULL NULL NULL
72495 46 45 NULL NULL NULL
72495 47 46 NULL NULL NULL
72495 50 49 NULL NULL NULL
72495 53 -1 509775 T09031367 005386
72495 53 52 509775 T09031358 005384

(Righe interessate: 46)

Sono riuscito ad ottenere questo risultato con questa query:

 Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

Nonostante il risultato sia quello voluto, c'è una vocina nella mia testa che mi assila dicendomi che l'approccio che ho avuto non è quello corretto, e che c'è una soluzione migliore e più semplice, qui a portata di mano, ma non riesco a trovarla....

Chiedo quindi a voi qualche suggerimento.

Grazie.

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: mer 10 feb 2010 - 21:28

>Come risultato voglio ottenere tutti i record presenti in SAN_CRT_DOCUMENTI
>con i campi colata, lotto e protocollo della tabella SAN_CRT_EXTRARIGHEDOC,
>legando i campi IdTesta, IdRiga, IdPosizione delle due tabelle,
>e i record presenti in SAN_CRT_EXTRARIGHEDOC che hanno stesso
>IdTesta e IdRiga di quelli presenti in SAN_CRT_DOCUMENTI ma posizioni
>diverse.

Ciao Carlo,

Quello che hai scritto corrisponde alla seguente query:

SELECT 
      E.IdTesta
    , E.IdRiga
    , E.Posizione
    , E.Colata
    , E.Lotto
    , E.Protocollo
    
FROM dbo.SAN_CRT_DOCUMENTI AS D
JOIN dbo.SAN_CRT_EXTRARIGHEDOC AS E
ON D.IdTesta = E.IdTesta
AND D.IdRiga = E.IdRiga
AND D.Posizione = E.Posizione

UNION ALL

SELECT 
      E.IdTesta
    , E.IdRiga
    , E.Posizione
    , E.Colata
    , E.Lotto
    , E.Protocollo
    
FROM dbo.SAN_CRT_DOCUMENTI AS D
JOIN dbo.SAN_CRT_EXTRARIGHEDOC AS E
ON D.IdTesta = E.IdTesta
AND D.IdRiga = E.IdRiga
AND D.Posizione <> E.Posizione
ORDER BY IdTesta, IdRiga, Posizione;

Ma il result set finale è completamente differente da quello restituito dalla tua query.

>Grazie.
Prego.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

autosblindo Profilo | Newbie

18 messaggi | Data Invio: gio 11 feb 2010 - 21:39

Ciao Lorenzo,

grazie per la cortese risposta.
Credo di aver esagerato ieri con l'inserimento di dati di esempio (in realtà erano i dati con cui mi stavo spaccando la testa ieri sera...).
Provo a spiegarmi meglio, con una versione ridotta di dati.

INSERT INTO SAN_CRT_DOCUMENTI (SessionId, IdTesta, IdRiga, Posizione)
VALUES ('123456789',  1000, 3, 3)
VALUES ('123456789',  1000, 4, -4)
VALUES ('123456789',  1000, 4, -3)
VALUES ('123456789',  1000, 4, 4)
VALUES ('123456789',  1001, 1, 1)
VALUES ('123456789',  1001, 2, -1)
VALUES ('123456789',  1001, 2, 2)
VALUES ('987654321',  1000, 5, 5)
VALUES ('987654321',  1001, 2, -1)
VALUES ('987654321',  1001, 2, 2)

e

INSERT INTO SAN_CRT_EXTRARIGHEDOC (IdTesta, IdRiga, Posizione, Colata, Lotto, Protocollo)
VALUES (1000,  4,  -2,  '514160',  'T09120604',  '005396')
VALUES (1000,  4,  -1,  '0585247',  'T09100749',  '005400')
VALUES (1000,  4,  4,  '0585247',  'T09100748',  '005398')
VALUES (1001,  1,  1,  '0585406',  'T10010672',  '005402')
VALUES (1001,  2,  2,  '0585294',  'T09120590',  '005406')
VALUES (1002,  1,  -1,  '0586311',  'T09110224',  '005391')
VALUES (1002,  1,  1,  '512797',  'T09091739',  '005399')
VALUES (1002,  2,  2,  '512797',  'T09091730',  '005397')

Questo è il risultato che voglio ottenere filtrando per SessionId = '123456789'
IdTesta IdRiga Posizione Colata Lotto Protocollo
--------------------------------------- ----------- ----------- -------------------------------------------------- -------------------------------------------------- ----------
1000 3 3 NULL NULL NULL
1000 4 -4 NULL NULL NULL
1000 4 -3 NULL NULL NULL
1000 4 -2 514160 T09120604 005396
1000 4 -1 0585247 T09100749 005400
1000 4 4 0585247 T09100748 005398
1001 1 1 0585406 T10010672 005402
1001 2 -1 NULL NULL NULL
1001 2 2 0585294 T09120590 005406

(Righe interessate: 9)

e per farlo utilizzo questa query (quella che ho scritto nel precedente post è errata):

SELECT D.IdTesta, D.IdRiga, D.Posizione,
E.Colata, E.Lotto, E.Protocollo
FROM SAN_CRT_DOCUMENTI AS D LEFT OUTER JOIN SAN_CRT_EXTRARIGHEDOC AS E ON D.IdTesta = E.IdTesta
AND D.IdRiga = E.IdRiga AND D.Posizione = E.Posizione
WHERE D.SessionId = '123456789'
UNION
SELECT E.IdTesta, E.IdRiga, E.Posizione,
E.Colata, E.Lotto, E.Protocollo
FROM SAN_CRT_EXTRARIGHEDOC AS E INNER JOIN SAN_CRT_DOCUMENTI AS D ON E.IdTesta = D.IdTesta
AND E.IdRiga = D.IdRiga AND D.SessionId = '123456789'
ORDER BY 1, 2, 3

Il punto è che questa soluzione non mi convince, e funziona solo perchè la union senza ALL mi elimina i duplicati che altrimenti sarebbero presenti.
Eseguite singolarmente la prima select è corretta, la seconda no. Dalla seconda select vorrei tutti i record che hanno stesso idTesta, stesso idRiga dei record della prima select (e che siano presenti nella prima select), ma posizioni diverse.
Con i dati d'esempio la seconda select dovrebbe restituirmi questi risultati:

IdTesta IdRiga Posizione Colata Lotto Protocollo
--------------------------------------- ----------- ----------- -------------------------------------------------- -------------------------------------------------- ----------
1000 4 -2 514160 T09120604 005396
1000 4 -1 0585247 T09100749 005400

(Righe interessate: 2)

Dopo diversi tentativi sono arrivato a questa variante (mi si è accesa una lampadina mentre scrivevo questo post):

 Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

Non l'ho ancora testata massicciamente, ma dai pochi test sembrerebbe funzionare.
E' la soluzione corretta? Ce ne sono altre migliori?

Grazie ancora, e spero di non avere esagerato.

Carlo.

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: gio 11 feb 2010 - 22:27

>E' la soluzione corretta? Ce ne sono altre migliori?

Ciao Carlo,

A me viene in mente questa:

WITH CTE_PosizioniDifferenti AS
(
    SELECT 
          E.IdTesta
        , E.IdRiga
        , E.Posizione
        , E.Colata
        , E.Lotto
        , E.Protocollo
        
    FROM dbo.SAN_CRT_DOCUMENTI AS D
    JOIN dbo.SAN_CRT_EXTRARIGHEDOC AS E
    ON D.IdTesta = E.IdTesta
    AND D.IdRiga = E.IdRiga
    AND D.Posizione <> E.Posizione
    WHERE D.SessionId = '123456789'

    EXCEPT

    SELECT 
          E.IdTesta
        , E.IdRiga
        , E.Posizione
        , E.Colata
        , E.Lotto
        , E.Protocollo
        
    FROM dbo.SAN_CRT_DOCUMENTI AS D
    JOIN dbo.SAN_CRT_EXTRARIGHEDOC AS E
    ON D.IdTesta = E.IdTesta
    AND D.IdRiga = E.IdRiga
    AND D.Posizione = E.Posizione
    WHERE D.SessionId = '123456789'
)
SELECT 
      D.IdTesta
    , D.IdRiga
    , D.Posizione
    , E.Colata
    , E.Lotto
    , E.Protocollo

FROM dbo.SAN_CRT_DOCUMENTI AS D 
LEFT JOIN dbo.SAN_CRT_EXTRARIGHEDOC AS E 
ON D.IdTesta = E.IdTesta
AND D.IdRiga = E.IdRiga 
AND D.Posizione = E.Posizione
WHERE D.SessionId = '123456789'

UNION ALL

SELECT *
FROM CTE_PosizioniDifferenti
ORDER BY IdTesta, IdRiga, Posizione;

>Grazie ancora, e spero di non avere esagerato.
Prego.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org