CODEWITHSUNDEEP
mysqlsqlselect
Vexator
Vexator

Reputation: 191

MySQL select pairs of data

I have two tables:

CREATE TABLE `digits` (
  `digit` tinyint(3) unsigned NOT NULL,
  `group` tinyint(3) unsigned NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO `digits` (`digit`, `group`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 2),
(5, 2);

and

CREATE TABLE `scores` (
  `digit1` tinyint(3) unsigned NOT NULL,
  `digit2` tinyint(3) unsigned NOT NULL,
  `score` tinyint(3) unsigned NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO `scores` (`digit1`, `digit2`, `score`) VALUES
(1, 2, 5),
(1, 4, 9),
(2, 3, 3);

what i would like to do, is to select all combinations of digits (without repetition) with scores and with null if there's no scores

example answer (WHERE group <=1):

digit1 | digit2 | score
-----------------------
1      | 2      | 5
1      | 3      | null
2      | 3      | 3

example answer (WHERE group <=2):

digit1 | digit2 | score
-----------------------
1      | 2      | 5
1      | 3      | null
1      | 4      | 9
1      | 5      | null
2      | 3      | 3
2      | 4      | null
2      | 5      | null
3      | 4      | null
3      | 5      | null
4      | 5      | null

Upvotes: 1

Views: 683

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

Try this:

select d1.digit, d2.digit, s.score
from digits d1 join
     digits d2
     on d1.group <= <groupnumber> and
        d2.group <= <groupnumber> and
        d1.digit < d2.digit left outer join
     score s
     on s.digit1 = d1.digit and
        s.digit2 = d2.digit

Upvotes: 2

Related Questions