SQL query for all possible pairs from a single column-CodePudding

I am working on an SQL query where I am looking for all possible combinations. My table looks like this:

Table: Fruits

|ID  |  Name |
|1   | Apple |
|2   | Banana|
|3   | Cherry|
|4   | Peach |

I am trying to get the out put of all possible combinations of two of the fruits with no repeats (ie, if there is Apple, Banana then I do not want Banana, Apple):

Apple, Apple
Apple, Banana
Apple, Cherry
...
Cherry, Peach
Peach, Peach

All I have so far is a way to make two rows of Fruits

SELECT Name AS Fruit1, Name AS Fruit2
FROM Fruits

Any help would be very appreciated!

CodePudding user response：

You can just join the tables. The syntax depends a little bit on the db you are using. This should work on most SQL databases:

   SELECT f1.name AS Fruit1,
          f2.name AS Fruit2
   FROM Fruits f1
   JOIN Fruits f2 ON f1.name <= f2.name

CodePudding user response：

Try the following cross join and filter

select t1.name, t2.name
from t t1
cross join t t2
where t2.id=t1.id or t2.id>t1.id
order by t1.id

CodePudding user response：

I did this with Postgresql but I think you can use the same logic with another database if you don't use postgres.

CREATE or replace FUNCTION get_str_ascii(char[]) RETURNS int8 AS $$
    DECLARE
      s int8 := 0;
      x char;
    BEGIN
      FOREACH x IN ARRAY $1
      LOOP
        s := s   ascii(x);
      END LOOP;
      RETURN s;
    END;
    $$ LANGUAGE plpgsql;

select MAX(tab.f1), MIN(tab.f2)  from (
    select 
        f1.name as f1, 
        f2.name as f2, 
        get_str_ascii(string_to_array(f1.name||f2.name, null)) as ascii_total from fruits f1
    inner join fruits f2 on true
) tab
group by tab.ascii_total

Calculate Ascii summatory of every fruit for that I created a function, it allowed me to know if two entries are repeated example the ascii summatory for applecherry is the same than for cherryapple 1183
Group by ascii total summatory column and get the MAX and MIN of every entries for example

fruit1 fruit2 ascii_total
cherry apple 1183
apple cherry 1183

If I get the max of fruit1 and fruit2 I would get

fruit1 fruit2
cherry cherry

for that reason I used MAX and MIN

I was stucking to figure out how to transform a string into a ascii summatory, this thread helped me: PostgreSQL - Convert string to ASCII integer