Cross Join Without Duplicate Combinations

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

mysql cross join, but without duplicated pair?

Simple, only join with values higher than the current one.

select r1.id, r2,id 
from rows r1 
cross join rows r2 
where r1.id < r2.id

Cross Join tables without duplicates - MYSQL

In many DBMSs this can be easily performed with FULL OUTER JOIN, but MySQL does not support it, so you must emulate.

You must gather all (FirstName, LastName, City) combinations which are present in at least one of the tables.

One of the emulation methods is the complete list obtaining via subquery with UNION which removes duplicates (combinations which are present in both tables):

SELECT FirstName, LastName, City,
       event1.AdultsInvited, event2.AdultsInvited  -- the same for another columns pairs
FROM ( SELECT FirstName, LastName, City 
       FROM event1
       UNION
       SELECT FirstName, LastName, City 
       FROM event2 ) total
LEFT JOIN event1 USING (FirstName, LastName, City)
LEFT JOIN event2 USING (FirstName, LastName, City)
ORDER BY FirstName, LastName, City;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=503367e1965fba94c74af56a3327372f

---

Another method uses a LEFT JOIN b UNION a RIGHT JOIN b, or a LEFT JOIN b UNION ALL a RIGHT JOIN b WHERE a.id IS NULL.

Remove duplicate combinations from cross join result in R

After full join, filter with inequality to avoid reverse duplicates:

df <- source_df %>% 
   full_join(source_df, by = character()) %>%
   filter(TableFrom < TableTo)

MySQL : Cannot remove duplicate combination in cross join

SELECT TRIM(REPLACE(REPLACE(city, 'Credit', ''), 'Loan', '')) city,
       GROUP_CONCAT(CASE WHEN LOCATE('Credit', city) THEN id END) credit_ids,
       SUM(CASE WHEN LOCATE('Credit', city) THEN fishing_segment END) credit_fishing_segment,
       GROUP_CONCAT(CASE WHEN LOCATE('Loan', city) THEN id END) loan_ids,
       SUM(CASE WHEN LOCATE('Loan', city) THEN fishing_segment END) loan_fishing_segment
FROM data
GROUP BY 1;

fiddle

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.

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

Mysql Cross Join without duplicates

Seems like you want a union so you would get the two tables as one result set but with no duplicates.

SELECT table1.name,table1.address,table1.type
FROM table1
union
SELECT table2.name,table2.address,table2.type,
FROM table2

spark cross join eliminate duplicate

In your example, you have a bunch of Tuple2[String, String]s, and the problem is that you want an equality that Tuple2 just doesn't have where (a, b) == (b, a). This is why distinct doesn't work for you. You therefore have to provide your own custom equality.

The thing is you don't want to override Tuple2's version of equals because that can be dangerous, so you can provide a custom equals somewhere:

def customEquals(tuple1: (String, String), tuple2: (String, String)) = {
  tuple1 == tuple2 || (tuple1._1 == tuple2._2 && tuple1._2 == tuple2._1)
}

Then you can use this function in a filter to get rid of your custom definition of duplicates:

val deduped = clientMatrixDs.filter {
  var seq = Seq.empty[(String, String)]
  tuple =>
    if (seq.exists(customEquals(tuple, _))) {
      false
    } else {
      seq :+= tuple
      true
    }
}

---

Related Topics