Mysql Cartesian Product: Each relationship only once

Question

I wonder if someone could help me here.

This is what I need to do: Pulling the IDs of the language table in new table with unique relationsships between all of them. Like the formula n(n-1) for nodes.

I tried with a cartesian product, but it doesn't work, because of column a = 1 and column b = 2 is the same as when column a = 2 and column b = 1. I am not sure if I can do this with a SQL statement, I wanted to avoid to do a workaround with a php script. So the SQL below is only half of the way.

DROP TABLE IF EXISTS lang_score;
CREATE TABLE lang_score
SELECT a.text_lang_id AS lang_1, b.text_lang_id AS lang_2 
FROM text_lang AS a, text_lang AS b 
WHERE a.text_lang_id != b.text_lang_id

I can't put a UNIQUE key on those columns because each number will appear more than once in both of them. I also tried with some SELECTS on this table, but I didn't find out how to avoid those duplicates of relationships.

Thanks in advance for any hint

EDIT

Query that works:

DROP TABLE IF EXISTS lang_score;
CREATE TABLE lang_score
SELECT a.text_lang_id AS lang_1, b.text_lang_id AS lang_2 
FROM text_lang AS a, text_lang AS b 
WHERE a.text_lang_id < b.text_lang_id

Use `<` instead of `!=`. That will return (1, 2) but not (2, 1). — Paul Spiegel, Mar 09 '17 at 17:38
Thank you - this is exactly what I was looking for - such a little change in the query with a great result! Ty a lot! — Ameliore, Mar 09 '17 at 18:31

score 0 · Answer 1 · answered Mar 09 '17 at 17:51

0

Could you try the following query:

DROP TABLE IF EXISTS lang_score;
CREATE TABLE lang_score
SELECT a.text_lang_id AS lang_1, b.text_lang_id AS lang_2 
FROM text_lang AS a, text_lang AS b 
GROUP BY a.text_lang_id, b.text_lang_id

answered Mar 09 '17 at 17:51

Darshan Mehta

30,102
11
68
102

Mysql Cartesian Product: Each relationship only once

1 Answers1