50
2018-12-27 18:37:29
0
-- tested on mysql 8.0
WITH prefix
AS
(
SELECT 1 AS no , 'klm' AS txt
UNION ALL SELECT 2, 'lee'
UNION ALL SELECT 3, 'seoul'
UNION ALL SELECT 4, 'incheon'
)
, data
AS
(
SELECT 1 AS name_no , 3 AS city_no
UNION ALL select 1, 4
UNION ALL select 2, 3
UNION ALL select 2, 4
)
SELECT aa.txt, cc.txt
FROM prefix AS aa
INNER JOIN data AS bb
ON aa.no = bb.name_no
INNER JOIN prefix AS cc
ON bb.city_no = cc.no
;