Trouver les éléments manquants par groupe dans une jointure

Version imprimable

Bonjour

Je ne sais pas si ce que je cherche a faire est possible en SQL mais je n'ai pas encore trouvé de solution

J'ai deux tables

- Sales Channel (3 elements : 1,2,3)
- SalesPrice

Pour chaque article présent dans SalesPrice je dois avoir un prix correspondant our Canaux définis sans Sales Channel 1,2 et 3
J'essaye donc de créer une jointure entre ces deux tables qui ferait apparaitre les articles n'ayant pas les prix correspondant a chaque canaux

Mais je sèche !

La requête ci dessous me sort une ligne (pour SalesChannel 1)
Code:

1 2 3 4 5 select * from SalesPrice FULL OUTER JOIN SalesChannel SC on SC.pkid=SalesPrice.SalesChannelId where SalesPrice.ArtId=22939
Mais j'aimerais voir apparaître 3 lignes dont deux auraient au moins L'article et le salesChannel, le reste null afin d'identifier les valeurs manquantes

Est-ce raisonnablement possible en SQL ?
Merci de votre aide

Voici le DDL de mes deux table
Code:

1 2 3 4 5 6 7 8 9 10 CREATE TABLE dbo.SalesChannel( pkId int IDENTITY(1,1) NOT NULL, Code varchar(32) NOT NULL, Name varchar(128) NOT NULL, Rank int NOT NULL, CONSTRAINT PK_SalesChannel PRIMARY KEY CLUSTERED ( pkId ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) )
Code:

1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE [dbo].[SalesPrice]( [pkId] [int] IDENTITY(1,1) NOT NULL, [ArtId] [int] NOT NULL, [SalesChannelId] [int] NULL, [Price] [decimal](8, 2) NULL, [LastUpdateBy] [varchar](64) NULL, [DateLastUpdate] [datetime] NOT NULL, CONSTRAINT [PK_SalesPrice] PRIMARY KEY CLUSTERED ( [pkId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) )

Bonjour,

Citation:

J'essaye donc de créer une jointure entre ces deux tables qui ferait apparaitre les articles n'ayant pas les prix correspondant a chaque canaux

En fait vous vouliez dire tous les canaux, c'est à dire les trois ;)

C'est effectivement possible, et je comprends que cela vous soit paru un peu tordu.
Les demi-jointures préservent une ligne par tuple correspondant, donc on ne peut pas obtenir l'ensemble des tuples possibles, même avec FULL JOIN.

Il est donc nécessaire de "générer" l'ensemble des tuples que l'on cherche, c'est à dire un produit cartésien : CROSS JOIN.
Une demi-jointure suffit alors à repérer les canaux pour lesquels il manque un prix.

J'ai fait un petit test avec le jeu de données suivant :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 INSERT INTO dbo.SalesChannel ( Code ,Name ,Rank ) VALUES ('Code Channel 1', ' Name Channel 1', 1) , ('Code Channel 2', ' Name Channel 2', 2) , ('Code Channel 3', ' Name Channel 3', 3) GO INSERT INTO dbo.SalesPrice ( ArtId ,SalesChannelId ,Price ,LastUpdateBy ,DateLastUpdate ) VALUES(1, NULL, 12, 'toto', GETDATE()) , (2, NULL, 15, 'titi', GETDATE()) , (3, 1, 5, 'tata', GETDATE()) , (4, 2, 52, 'tutu', GETDATE()) , (5, 3, 65, 'tyty', GETDATE()) GO
Voici la requête :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT SP.ArtId , SP.DateLastUpdate , SP.Price , SC.Code , SC.Name , SPC.* FROM dbo.SalesPrice AS SP CROSS JOIN dbo.SalesChannel SC LEFT JOIN dbo.SalesChannel AS SPC ON SPC.pkid = SP.SalesChannelId --WHERE SP.ArtId=1 ORDER BY SP.ArtId
Quelques remarques cependant, car il me semble qu'il y a quelques écarts dans le modèle physique :

Il n'y a pas de contrainte de clé étrangère sur la colonne SalesChannelId de la table dbo.SalesPrice référençant la table dbo.SalesChannel
Pourquoi permettre que la colonne SalesChannelId de la table dbo.SalesPrice n'ait pas de valeur ? (i.e. NULLable)
Il devrait probablement y avoir une contrainte d'unicité sur chacune 3 colonnes métier de la table dbo.SalesChannel
Est-il possible que la colonne Rank de la table dbo.SalesChannel contienne de valeurs supérieures à 255 ou inférieures à zéro ? Si non, vous pouvez utiliser le type tinyint ;)
Il devrait probablement y avoir une contrainte d'unicité sur les deux colonnes ArtId et SalesChannelId de la table dbo.SalesPrice
Il est probable qu'un prix ne soit pas jamais négatif : si tel est le cas, il manque une contrainte de domaine sur la colonne Price de la table dbo.SalesPrice
La colonne LastUpdateBy de la table dbo.SalesPrice est de type varchar(64), ce qui laisse supposer que l'on stockera le nom de l'utilisatuer qui a réalisé le dernier changement. Il serait bien moins coûteux, à tout point de vue, de stocker l'id de cet utilisateur ;)

Il en reste une, mais elle tellement énorme que je suppose que vous avez donné un coup de ciseau dans les relations pour donner un DDL smiplifié de vos tables et demander de l'aide ;)

@++ ;)

15/01/2017, 23h49
olibara

Merci Elsuket

Effectivement en poursuivant mes recherche j'etais arrivé a la conclusion que le CROSS JOIN etait la solution
Je m’apprêtais a ajouter un commentaire quand j'ai trouvé ta réponse pour laquelle je n'avais curieusement pas reçu de notification

Toutes tes autres remarques sont très pertinentes mais ont toutes leurs explication

- Simplification volontaire du modèle en phase de prototype
- Rabotage du DDL pour l'exemple

Et pour le UserID, les sources d'identity pouvant etre maintenue exterieurement a la DB opérationelle, le VarChar est plus confortable

NB : Je n'utilise presque plus jamais autre choses que le INT ou parfois BigINT si vraiment nécessaire !

Autrefois j'étais maniaque de l'optimisation des ressources mais j'ai fini par me dire que l'effort n'en valait plus la peine !
17/01/2017, 21h27
elsuket

Citation:

Autrefois j'étais maniaque de l'optimisation des ressources mais j'ai fini par me dire que l'effort n'en valait plus la peine !

Qu'est-ce qui t'a conduit à cela ?

@++ ;)
17/01/2017, 22h35
olibara

Citation:

Qu'est-ce qui t'a conduit à cela ?

La réflexion que les processeur sont de toute façon optimisé pour traiter des int32 (au moins)
Que l'espace disque ne vaut plus grand chose
Et que ce contexte l'effort de couper des int en bytes et bytes en bit n'apporte pas grand chose de plus que la satisfaction intellectuelle de l'avoir fait !

Sauf dans des contexte particulier avec des contraintes matérielles importantes !
18/01/2017, 00h43
elsuket

Citation:

Que l'espace disque ne vaut plus grand chose

Certes, mais je ne crois pas que ce soit une raison pour le gaspiller. Plus on en met en RAM, meilleures seront les IOs et donc les temps de réponse ;)
D'ailleurs, cela se retrouve chez les éditeurs de logiciels dans les copies d'environnements de développement et de test.

Citation:

l'effort de couper des int en bytes et bytes en bit n'apporte pas grand chose de plus que la satisfaction intellectuelle de l'avoir fait

Là encore il s'agit de tailler au plus juste pour minimiser les IOs, pas d'un délire de rêveur ou d'une personne physcho-rigide :ptdr:

Le stockage reste la partie la plus lente du hardware, même si elle évolue constamment, et que les disques SSD ont apporté un bond en avant énorme.
D'ailleurs, la mécanique interne de l'implémentation du stockage orienté colonne dans SQL Server montre que c'est bien d'actualité, puisque les valeurs des colonnes sont implicitement compressées.
L'unique but que cela sert est la minimisation des IOs.

Je constate toujours que construire une base de données sans prendre le soin de la modéliser et de choisir avec attention les types de données les mieux adaptés pose toujours des problèmes de performance non-négligeables lors des montées en volumes et en charge. J'observe cela aussi bien sur du stockage HDD que sur du SSD. Maintenant, si ça marche pour vous, je n'y vois pas d'inconvénient.

@++ ;)