0

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!

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
MustachAsh
  • 1

3 Answers3

1

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
fhossfel
  • 2,041
  • 16
  • 24
  • The OP want's *unordered* pairs: "... if there is Apple, Banana then I do not want Banana, Apple ...". This solution doesn't respect that. – sticky bit Dec 01 '21 at 21:42
  • Thanks. I missed that. I have updated the example. – fhossfel Dec 01 '21 at 21:45
  • 1
    Now "Apple, Apple" will be missing, they have that in their expected result... And I suggest to put the expression in the `ON` clause rather than the `WHERE` clause. It's clearer there. – sticky bit Dec 01 '21 at 21:47
0

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
Stu
  • 30,392
  • 6
  • 14
  • 33
0

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
  1. 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
  2. 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