This is an extension to my previous post.
WITH cte1 AS (
SELECT * FROM Combination
WHERE Col1 = 'val' and city='karim'),
cte2 AS (
SELECT * FROM Combination
WHERE Col1 = 'val2' and city='karim')
SELECT CONCAT(cte1.Col2, cte2.Col2) AS Result
FROM cte1 CROSS JOIN cte2;
| col1 | col2 | City |
|---|---|---|
| Val | 145 | Telang |
| val2 | 13 | Telang |
| val2 | 25 | Telang |
| val | 146 | karim |
| val2 | 124 | karim |
| val2 | 56 | karim |
Output:
| Result |
|---|
| 14513 |
| 14525 |
| 146124 |
| 14656 |
There are multiple cities.I wanted to get combinations only for the values existing in the cities
Tried something like this, but does not work.
SELECT * FROM Combination
WHERE Column1 = 'value' and city IN(select city from Combinations);
CodePudding user response：
Use an INNER self join of the table:
SELECT CONCAT(c1.Col2, c2.Col2) AS Result
FROM Combination c1 INNER JOIN Combination c2
ON c2.city = c1.city
WHERE c1.Col1 = 'val' AND c2.Col1 = 'val2';