Trouble with SQL query with cartesian product

Question

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?

Answer 1

SELECT
  COUNT(z.idx) count,
  x.id idx,
  y.id idy
FROM
  (x CROSS JOIN y)
  LEFT JOIN z ON z.idx = x.id AND z.idy = y.id
GROUP BY
  x.id,
  y.id
ORDER BY
  COUNT(z.idx) DESC,
  x.id,
  y.id

Answer 2

This seems to work:

SELECT COUNT(*) AS CNT, IDX, IDY
FROM Z
GROUP BY IDX, IDY
UNION
SELECT 0, X.ID, Y.ID
FROM X, Y
WHERE NOT EXISTS (
    SELECT * FROM Z WHERE Z.IDX = X.ID AND Z.IDY = Y.ID
)
ORDER BY CNT DESC

Answer 3

SELECT
    (SELECT COUNT(*) FROM Z WHERE IDX = X.ID AND IDY = Y.ID),
    X.ID,
    Y.ID
FROM
    X,Y

That's your answer... why do you possibly want that query, no clue :)

Answer 4

SELECT (SELECT(COUNT(*) FROM Z) AS COUNT, X.ID AS IDX, y.ID AS IDY FROM X CROSS JOIN Y ORDER BY 1 DESC, 2, 3