Make Unique combinations using mysql

Question

I have two tables - table1 & table2 as below :

table1

id | name
1  | color
2  | size

table2

id | table1id | name
1  | 1        | white
2  | 1        | red
3  | 2        | small
4  | 2        | medium
5  | 2        | large

In both table first id column in primary key. How can i get unique combination from both tables. Please suggest query to get the result.

expected output:

white       | small
white       | medium
white       | large
red         | small
red         | medium
red         | large

Answer 1

PROPOSED QUERY

SELECT T1.name,T2.name FROM
(SELECT B.name FROM table1 A
INNER JOIN table2 B ON A.id=B.table1id
WHERE A.name='color') T1 INNER JOIN
(SELECT B.name FROM table1 A
INNER JOIN table2 B ON A.id=B.table1id
WHERE A.name='size') T2
ORDER BY T1.name,T2.name;

SAMPLE DATA

DROP DATABASE IF EXISTS rush1312;
CREATE DATABASE rush1312;
USE rush1312
CREATE TABLE table1
(id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
PRIMARY KEY (id));
INSERT INTO table1 (name) VALUES ('color'),('size');
CREATE TABLE table2
(id INT NOT NULL AUTO_INCREMENT,
table1id INT NOT NULL,
name VARCHAR(20),
PRIMARY KEY (id));
INSERT INTO table2 (table1id,name) VALUES
(1,'white'),(1,'red'),(2,'small'),(2,'medium'),(2,'large');

SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS rush1312;
Query OK, 2 rows affected (0.55 sec)

mysql> CREATE DATABASE rush1312;
Query OK, 1 row affected (0.00 sec)

mysql> USE rush1312
Database changed
mysql> CREATE TABLE table1
    -> (id INT NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20),
    -> PRIMARY KEY (id));
Query OK, 0 rows affected (0.30 sec)

mysql> INSERT INTO table1 (name) VALUES ('color'),('size');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE table2
    -> (id INT NOT NULL AUTO_INCREMENT,
    -> table1id INT NOT NULL,
    -> name VARCHAR(20),
    -> PRIMARY KEY (id));
Query OK, 0 rows affected (0.34 sec)

mysql> INSERT INTO table2 (table1id,name) VALUES
    -> (1,'white'),(1,'red'),(2,'small'),(2,'medium'),(2,'large');
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT T1.name,T2.name FROM
    -> (SELECT B.name FROM table1 A
    -> INNER JOIN table2 B ON A.id=B.table1id
    -> WHERE A.name='color') T1 INNER JOIN
    -> (SELECT B.name FROM table1 A
    -> INNER JOIN table2 B ON A.id=B.table1id
    -> WHERE A.name='size') T2
    -> ORDER BY T1.name,T2.name;
+-------+--------+
| name  | name   |
+-------+--------+
| red   | large  |
| red   | medium |
| red   | small  |
| white | large  |
| white | medium |
| white | small  |
+-------+--------+
6 rows in set (0.00 sec)

GIVE IT A TRY !!!

Answer 2

Try this, it will give you the specific output as you expect:

SELECT t1.name AS color, t2.name AS size
FROM  table2 t1 
JOIN table2 t2 ON t1.table1id <> t2.table1id 
WHERE t1.table1id=1 AND t2.table1id=2 ORDER BY t1.name DESC, t2.name DESC;

Answer 3

Self join table2 table with all records where id is not the same and both match where conditions.

select t2.name, t3.name
from   table2 t2
join   table2 t3
on     t2.id <> t3.id
and    t2.table1id = t3.table1id
where  t2.table1id = 1;

name  | name 
:---- | :----
red   | white
white | red  

select t2.name, t3.name
from   table2 t2
join   table2 t3
on     t2.id <> t3.id
and    t2.table1id = t3.table1id
where  t2.table1id = 2;

name   | name  
:----- | :-----
medium | small 
large  | small 
small  | medium
large  | medium
small  | large 
medium | large 

dbfiddle here