CODEWITHSUNDEEP
sqlpostgresql-9.6cross-join
user-2147482428
user-2147482428

Reputation: 167

Cross join returning too few rows

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions