Mysql – Make Unique combinations using thesql

MySQL

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

Best Answer

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 !!!

Related Question