OUTER JOIN To Find missing element in Groups

1

Hello
I have two table
Sales Channel (3 elements : 1,2,3)
SalesPrice
For each Article I must have 3 SalesPrice (1 for each Sales Channel)
I'm trying to create a Query to find Missing Channels for each article in SalesPrice but I'm lost

For Example
This only gives me one row (for SalesChannel 1)

select
*
from SalesPrice
FULL OUTER JOIN SalesChannel SC on SC.pkid=SalesPrice.SalesChannelId
where SalesPrice.ArtId=22939

But it should gives me 3 row (2 and 3 are missing)

Thanks for any help

CREATE TABLE [dbo].[SalesChannel](
[pkId] [int] IDENTITY(1,1) NOT NULL,

[Code] varchar NOT NULL,
[Name] varchar 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)
)

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 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)
)

2

Try this:

select a.code
      ,a.name
      ,b.artid
  from dbo.saleschannel as a
       cross apply (select distinct artid from dbo.salesprice) as b
       left outer dbo.salesprice as c
               on c.saleschannelid=a.pkid
              and c.artid=b.artid
 where c.pkid is null
;
3

thanks a lot bitsmed

Indeed I've missed the cross method !
It works like a charm

Just a little fix in your code

left outer JOIN dbo.salesprice as c