CODEWITHSUNDEEP
sqlt-sql
Jonas
Jonas

Reputation:

Trouble with SQL query with cartesian product

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?

Upvotes: 2

Views: 1380

Answers (4)

Maxam
Maxam

Reputation: 4051

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

Upvotes: 0

Timothy Khouri
Timothy Khouri

Reputation: 31865

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

Upvotes: 1

splattne
splattne

Reputation: 104050

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

Upvotes: 1

Tomalak
Tomalak

Reputation: 338326

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

Upvotes: 0

Related Questions