3

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

3 Answers 3

2

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

1

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;
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.