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