Combinations (Not Permutations) from Cross Join in SQL

combinations (not permutations) from cross join in sql

T as t1
inner join
T as t2
  on t1.field < t2.field

FWIW, you can just use INNER JOIN for this, it's not strictly a CROSS JOIN. MySQL (and perhaps some other RDBMS) treats these two types of join as identical, but in ANSI SQL, a cross join has no join condition -- it's a deliberate Cartesian product.

Cross Join without duplicate combinations

select A.id aid,B.id bid
from A inner join B on a.id <= b.id
union
select B.id,A.id
from A inner join B on b.id < a.id

If you wanted to be more sophisticated:

select distinct
       case when a.id<=b.id then a.id else b.id end id1,
       case when a.id<=b.id then b.id else a.id end id2
from A cross join B

In my little unscientific bake off with tiny tables, the latter was faster. And below, the case expressions written as subqueries.

select distinct
       (select MIN(id) from (select a.id union select b.id)[ ]) id1,
       (select MAX(id) from (select a.id union select b.id)[ ]) id2
from A cross join B

permutation and combination

If by identical you mean pairs like (white, white) perhaps this is what you want:

SELECT a.color, b.color
FROM colors a
CROSS JOIN colors b
WHERE a.color != b.color

If by identical you additionally mean preserve only one of (white, black) or (black, white) perhaps this is what you want:

SELECT a.color, b.color
FROM colors a
CROSS JOIN colors b
WHERE a.color > b.color

The important part is to reject the elements you don't want after you perform the cross-join.

Note that this won't create any new tables or modify existing ones. a and b are merely two different aliases for the same table colors. The table has only one column color, but since the table is present twice in the SELECT, you need to distinguish both (conceptual, not factual!) instances of the colors table.

You can't do without a join (then you'd have too few rows), nor you can easily do without aliases (you have to refer to both columns to reject some rows) nor there is a reason to assign aliases.

Cross join remaining combinations

EDIT:

I thought of another cleaner solution. Do a cross join first, then a right join on the customer_id and product_name, and coalesce the product statuses.

SELECT customer_id, product_name, coalesce(product_status, status_1)
FROM products p
RIGHT JOIN (
    SELECT * 
    FROM (SELECT DISTINCT customer_id FROM products) pro
    CROSS JOIN portfolio
) pt
USING (customer_id, product_name)
ORDER BY customer_id, product_name

Old answer:
The idea is to include information of all product names for a customer_id into a list, and check whether the product in portfolio is in that list.

(SELECT customer_id, pt_product_name as product_name, first(status_1) as product_status
FROM (
    SELECT
        customer_id,
        p.product_name as p_product_name,
        pt.product_name as pt_product_name,
        product_status,
        status_1,
        status_2,
        collect_list(p.product_name) over (partition by customer_id) AS product_list
    FROM products p
    CROSS JOIN portfolio pt
    )
WHERE NOT array_contains(product_list, pt_product_name)
GROUP BY customer_id, product_name)

UNION ALL

(SELECT customer_id, p_product_name as product_name, first(product_status) as product_status
FROM (
    SELECT
        customer_id,
        p.product_name as p_product_name,
        pt.product_name as pt_product_name,
        product_status,
        status_1,
        status_2,
        collect_list(p.product_name) over (partition by customer_id) AS product_list 
    FROM products p
    CROSS JOIN portfolio pt)
WHERE array_contains(product_list, pt_product_name)
GROUP BY customer_id, product_name)

ORDER BY customer_id, product_name;

which gives

+-----------+------------+--------------+
|customer_id|product_name|product_status|
+-----------+------------+--------------+
|          1|           A|        Active|
|          1|           B|    Inelegible|
|          1|           C|    Ineligible|
|          1|           D|    Inelegible|
|          2|           A|    Inelegible|
|          2|           B|        Active|
|          2|           C|        Active|
|          2|           D|    Inelegible|
|          3|           A|     Cancelled|
|          3|           B|    Inelegible|
|          3|           C|    Ineligible|
|          3|           D|    Inelegible|
+-----------+------------+--------------+

FYI the chunk before UNION ALL gives:

+-----------+------------+--------------+
|customer_id|product_name|product_status|
+-----------+------------+--------------+
|          1|           B|    Inelegible|
|          1|           C|    Ineligible|
|          1|           D|    Inelegible|
|          2|           A|    Inelegible|
|          2|           D|    Inelegible|
|          3|           B|    Inelegible|
|          3|           C|    Ineligible|
|          3|           D|    Inelegible|
+-----------+------------+--------------+

And the chunk after UNION ALL gives:

+-----------+------------+--------------+
|customer_id|product_name|product_status|
+-----------+------------+--------------+
|          1|           A|        Active|
|          2|           B|        Active|
|          2|           C|        Active|
|          3|           A|     Cancelled|
+-----------+------------+--------------+

Hope that helps!

SQL: How to generate non-existing combinations

you can use cross join to have all possible combinations, then with the condition, you can remove the already existed ones.

Select * from words a cross join words b 
where not exists (select * from combinations c where c.first_id = a.id and c.second_id = b.id) 

SQL: self join using each rows only once

Changing the JOIN condition slightly will achieve what you want..

Instead of:

ON (mytable.letter = self.letter and mytable.number != self.number)

use

ON (mytable.letter = self.letter and mytable.number > self.number)

This will only include combinations where self.number is greater than mytable.number which in effect restricts the results to one valid ordering of each combination...

SQL Server - all possible combinations of Bit columns

Use a CTE and CROSS JOIN it with itself 7 times, as in:

with bits as (select 0 as bit union select 1)
select
  row_number() over(
    order by a.bit, b.bit, c.bit, d.bit, e.bit, f.bit, g.bit) as id,
  a.bit, b.bit, c.bit, d.bit, e.bit, f.bit, g.bit
from bits a
cross join bits b
cross join bits c
cross join bits d
cross join bits e
cross join bits f
cross join bits g

Duplicate pairs in SQL cross join?

You can join those p2's that are CIS differently to those that aren't. One way is as follows.

select
  p1.investigator,
  p2.investigator
from
  proposals AS p1
    cross join
  proposals AS p2 
where
  p1.investigatorDepartment = 'CIS' and ((
      p2.investigatorDepartment = 'CIS' and 
      p1.investigator < p2.investigator
  ) or (
      p2.investigatorDepartment != 'CIS' or
      p2.investigatorDepartment is null
  )) and
  p1.proposalNum = p2.proposalNum;

SQL Fiddle

Return all possible combinations of values on columns in SQL

Assuming at least SQL 2005 for the CTE:

;with cteAllColumns as (
    select col1 as col
        from YourTable
    union
    select col2 as col
        from YourTable
)
select c1.col, c2.col 
    from cteAllColumns c1 
        cross join cteAllColumns c2 
    where c1.col < c2.col
    order by c1.col, c2.col

---

Related Topics