I am struggling with a creating a query. It is related to a large and complicated database but for the sake of this post I have boiled the problem down to something simpler.
I have three tables X, Y, Z defined as
CREATE TABLE [dbo].[X](
[ID] [bigint] NOT NULL
)
CREATE TABLE [dbo].[Y](
[ID] [nchar](10) NOT NULL
)
CREATE TABLE [dbo].[Z](
[IDX] [bigint] NOT NULL,
[IDY] [nchar](10) NOT NULL
)
They contain the following data
Table X Table Y Table Z
ID ID IDX IDY
-- -- --- ---
1 A 1 A
2 B 1 B
3 C 1 A
I want to create a query that produces the following result
Count IDX IDY
===== === ===
2 1 A
1 1 B
0 1 C
0 2 A
0 2 B
0 2 C
0 3 A
0 3 B
0 3 C
My initial thought was
SELECT COUNT(*), X.ID, Y.ID
FROM
X
CROSS JOIN Y
FULL OUTER JOIN Z ON X.ID = Z.IDX AND Y.ID = Z.IDY
GROUP BY X.ID, Y.ID
but this turns out to be on the wrong road.
Any ideas?