Question 112882 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Get combinations of a columns

I have two columns and i wanted to display all the combinations of a single columns

Table 1

column1 column2
value 145
value 146
value2 13
value2 56
value2 364

enter image description here

I have used selfjoin, but that doesnot workout.

SELECT a.column1 FROM table1 a CROSS JOIN table1 b where column1="value"

Any suggestions would be appreciated

Tags:

2

Answers


  1. 0

    Check this solution:

    CREATE TABLE Combination (Column1 VARCHAR(20), Column2 INT);

INSERT INTO Combination VALUES ('value', 145), ('value', 146),('value2', 13), ('value2', 56), ('value2', 364);

+---------+---------+
| Column1 | Column2 |
+---------+---------+
| value   | 145     |
| value   | 146     |
| value2  | 13      |
| value2  | 56      |
| value2  | 364     |
+---------+---------+
5 rows in set (0.00 sec)

    Now Full SQL Code:

    WITH cte1 AS (SELECT * FROM Combination WHERE Column1 = 'value') , cte2 AS (SELECT * FROM Combination WHERE Column1 = 'value2') 
SELECT CONCAT(cte1.Column2, cte2.Column2) AS Result FROM cte1 CROSS
 JOIN cte2;

+--------+
| Result |
+--------+
| 14613  |
| 14513  |
| 14656  |
| 14556  |
| 146364 |
| 145364 |
+--------+
6 rows in set (0.00 sec)

    Update : I changed the column2 datatype to (INT).
    Screenshot I got:

    Terminal

    Update2 requested from OP:

    CREATE TABLE NewTable (Col1 TEXT);

INSERT INTO NewTable  WITH cte1 AS (SELECT * FROM Combin WHERE Column1 = 'value') , cte2 AS (SELECT * FROM Combin WHERE Column1 = 'value2') SELECT CONCAT(cte1.Column2, cte2.Column2) AS Result FROM cte1 CROSS  JOIN cte2;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM NewTable;
+--------+
| Col1   |
+--------+
| 14613  |
| 14513  |
| 14656  |
| 14556  |
| 146364 |
| 145364 |
+--------+
6 rows in set (0.00 sec)
    Login or Signup to reply.
  2. 0

    few of them doesnot get concatinated and having this. is it possible to use case condition as well for ex:

    CASE COLUMN2 ..
THEN
END...
SELECT CONCAT(cte1.Column2, cte2.Column2) AS Result FROM cte1 CROSS
 JOIN cte2;

| Result   | 
| -------- | 
| 001-     | 
| 123-     |
| 456-     |
| 789-     |
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search