Reputation: 167
I have two tables below:
Table A
+--------+--------+--------+
| color | shape | size |
+--------+--------+--------+
| Red | Square | Small |
| Blue | Square | Small |
| Yellow | Square | Small |
| Red | Circle | Small |
| Blue | Circle | Small |
| Yellow | Circle | Small |
| Yellow | Square | Medium |
| Red | Circle | Medium |
| Blue | Circle | Medium |
| Yellow | Circle | Medium |
| Red | Square | Large |
| Red | Circle | Large |
| Blue | Circle | Large |
| Yellow | Circle | Large |
+--------+--------+--------+
Table B
+---------------+
| edge |
+---------------+
| Straight Line |
| Dotted Line |
| Squiggly Line |
+---------------+
I am running the following query:
WITH TableA (color, shape, size)
AS (SELECT DISTINCT color, shape, size
FROM TableA),
TableB (edge)
AS (SELECT DISTINCT edge
FROM TableB)
SELECT COUNT(*) FROM
(SELECT a.*,
b.*
FROM TableA AS a
CROSS JOIN TableB AS b) as x;
What I expect to happen: it will select all the distinct values from each column in Table A (color, shape, size), then select each unique value from each column in Table B (edge), then do a cross join producing every combination of color, shape, size, and edge.
I've checked that the part where the query selects unique values is working independently, but running it all together produces fewer rows than I am expecting.
With 3 colors, 2 shapes, 3 sizes, and 3 edges, I should get 54 possible combinations/rows, but am only getting 42.
Upvotes: 0
Views: 649
Reputation: 1271231
You need to select the columns independently:
select c.olor, s.shape, sz.size, e.edge
from (select distinct color from a) c cross join
(select distinct shape from a) s cross join
(select distinct size from a) sz cross join
(select distinct edge from b) e
Upvotes: 1