CODEWITHSUNDEEP
mysqlsql
Ryoss
Ryoss

Reputation: 59

Produce a list of colours against all other colours

As the title may suggest, I'm in need for some guidance. Currently what I have is rather close, it's just that one of each colour is repeating itself and I cant seems to get my head around it.

Here is my table:

  CREATE TABLE Colors
   (c_ID VARCHAR2(3) NOT NULL,
    c_NAME VARCHAR2(11));

  INSERT INTO Colors VALUES
    ('T01','RED');
  INSERT INTO Colors VALUES
    ('T02','BLUE');
  INSERT INTO Colors VALUES
    ('T03','BLACK');
  INSERT INTO Colors VALUES
    ('T04','YELLOW');
  INSERT INTO Colors VALUES
    ('T05','ORANGE');

The query that I used:

 select distinct a.c_name as "HOME", s.c_name as "AWAY"
 from colors a, colors s
 order by a.c_name;

The results come as:

 Black     Black
 Black     Blue
 Black     Orange
 Black     Red
 Black     Yellow

This happens for each colour, but as you can see there is always a scenario where each colour repeats against itself. How can I get rid of that without deleting? Thanks in advance.

Upvotes: 1

Views: 66

Answers (1)

Tom Chantler
Tom Chantler

Reputation: 14941

You could just change your query like this:

select distinct a.t_name as "HOME", s.t_name as "AWAY"
from teams a, teams s
where a.t_name <> s.t_name -- or this if you need to check on id instead a.t_id <> s.t_id
order by a.t_name;

Upvotes: 2

Related Questions