Search code examples
mysqljoinconcatenation

CTE passing multiple subqueries

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);
Solution

  • 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';