CODEWITHSUNDEEP
mysql
AaronDT
AaronDT

Reputation: 4060

MySQL return possible pairs from columns

I have the following table

Name | Surname | Gender
John | A       |  M
Anna | B       |  F
Sam  | C       |  M
Lisa | D       |  F

Now I would like to return all possible combinations of pairs, with the restriction that each name should not pair with itself. In addition I would like to display information about whether both pairs share the same gender or not.

As such, the output should look as follows

Pair           | Same gender?
John A, Anna B | No
John A, Sam C  | Yes
...            | ...
Lisa D, Sam C  | No

How would I do that in MySQL?

Upvotes: 2

Views: 164

Answers (1)

CodeLikeBeaker
CodeLikeBeaker

Reputation: 21312

You can do a cross join in mysql like this:

SELECT
    a.`name`
    , b.name
    ,
    CASE
        WHEN a.gender != b.gender
        THEN 'No'
        ELSE 'Yes'
    END as 'Same Gender'
FROM
    gender a
    , gender b
WHERE a.name != b.name

And if you want to "Pair" up the columns like your question, you can do the column like this:

CONCAT(a.`name`, ', ', b.`name`) AS 'Pair'

To answer your comment you can do it like this:

SELECT
    CONCAT(a.`name`, ', ', b.`name`) AS 'Pair'
    , mi_a.stuff
    , mi_b.stuff
    ,
    CASE
        WHEN a.gender != b.gender
        THEN 'No'
        ELSE 'Yes'
    END AS 'Same Gender'
FROM
    test.gender a
    CROSS JOIN test.gender b
    JOIN test.`more_info` mi_a ON mi_a.gender_id = a.id
    JOIN test.`more_info`  mi_b ON mi_b.gender_id = b.id
WHERE a.name != b.name

Upvotes: 2

Related Questions