I came across a use case where I wanted to generate a combination table where the attributes of a record had to be matched with all other records in the table. Something like creating list of edges in a Complete Graph using a nodes table.
To give you an example of the requirement, you can consider the following table (Postgres).
create table test_users ( id uuid default uuid_generate_v4(), first_name text, last_name text );
I added some records into the table using the following insert queries
INSERT INTO public.test_users (id, first_name, last_name) VALUES ('3ef309db-07cd-4e3f-95db-406f8165e54d', 'john', 'doe'); INSERT INTO public.test_users (id, first_name, last_name) VALUES ('50f33b51-1d3a-463c-9d5a-6f0b2c15cba2', 'marry', 'poppins'); INSERT INTO public.test_users (id, first_name, last_name) VALUES ('9812fbcf-d269-47a4-801d-2a8916e114e1', 'tony', 'stark'); INSERT INTO public.test_users (id, first_name, last_name) VALUES ('8d7b8d00-9602-4a12-914d-3df6a40d2716', 'idris', 'alba'); INSERT INTO public.test_users (id, first_name, last_name) VALUES ('2dee874f-f41e-4d68-a341-45e0a18079df', 'john', 'frusciante');
And you’ll get the following table in place
id | first_name | last_name --------------------------------------+------------+------------ 3ef309db-07cd-4e3f-95db-406f8165e54d | john | doe 50f33b51-1d3a-463c-9d5a-6f0b2c15cba2 | marry | poppins 9812fbcf-d269-47a4-801d-2a8916e114e1 | tony | stark 8d7b8d00-9602-4a12-914d-3df6a40d2716 | idris | alba 2dee874f-f41e-4d68-a341-45e0a18079df | john | frusciante
Now I want to create a combination of these names in such a way that the first_name
of a row should have all the possible combination available such that (f1, f2) = (f2, f1)
. To give you some more context the expected output should be as follows.
first_name | first_name | last_name | last_name ------------+------------+------------+------------ john | marry | doe | poppins john | tony | doe | stark john | idris | doe | alba john | john | doe | frusciante marry | tony | poppins | stark marry | idris | poppins | alba marry | john | poppins | frusciante tony | idris | stark | alba tony | john | stark | frusciante idris | john | alba | frusciante
You can see that the combination of column 1 and column 2 for first_name
is unique in the table.
The closest that I was able to reach was to have an inner join using the following query.
select t1.first_name, t2.first_name, t1.last_name, t2.last_name from test_users t1 left join test_users t2 on t1.id <> t2.id;
But this yields all the records where (f1, f2) != (f2, f1)
hence creating redundant records. Is there a way to get unique list of edges from the list of nodes in a Complete Graph using SQL?
Advertisement
Answer
Just use <
(or >
) instead of <>
in the join condition:
select t1.first_name first_name1, t2.first_name first_name2, t1.last_name last_name1, t2.last_name last_name2 from test_users t1 inner join test_users t2 on t1.id < t2.id
Notes:
you probably want to alias the column in the resultset to disambiguate the columns coming from both tables
no need for a
left join
– aninner join
does what you want