Make Unique combinations using mysql I have two tables - table1 &amp; 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
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&gt; DROP DATABASE IF EXISTS rush1312;
Query OK, 2 rows affected (0.55 sec)

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

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

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

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

mysql&gt; INSERT INTO table2 (table1id,name) VALUES
    -&gt; (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&gt;


PROPOSED QUERY EXECUTED

mysql&gt; SELECT T1.name,T2.name FROM
    -&gt; (SELECT B.name FROM table1 A
    -&gt; INNER JOIN table2 B ON A.id=B.table1id
    -&gt; WHERE A.name='color') T1 INNER JOIN
    -&gt; (SELECT B.name FROM table1 A
    -&gt; INNER JOIN table2 B ON A.id=B.table1id
    -&gt; WHERE A.name='size') T2
    -&gt; 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 !!!