Without association I want to fetch records from two different table-CodePudding

Without association I want to fetch records from two different table.

Note:

In name column, to identify I want to add table name. (i.e: t1 test1)
There is no relationship between two tables

TABLE A
id  name    email
1   test1   [email protected]
2   test2   [email protected]
3   test3   [email protected]
4   test4   [email protected]

TABLE B
id  name    email
1   test1   [email protected]
2   test2   [email protected]
3   test3   [email protected]
4   test4   [email protected]

RESULT

id   name    email

1   t1-test1   [email protected]
2   t1-test2   [email protected]
3   t1-test3   [email protected]
4   t1-test4   [email protected]
5   t2-test1   [email protected]
6   t2-test2   [email protected]
7   t2-test3   [email protected]
8   t2-test4   [email protected]

My try so far:

SELECT  distinct table1.id, table1.name, table.email, table2.id, table1.name, table.email
FROM table1, table2

Above query giving repeated records

CodePudding user response：

WITH cte AS (
(
        SELECT
            't1-' || a.name AS name,
            a.email
        FROM
            a)
    UNION
    SELECT
        't2-' || b.name,
        b.email
    FROM
        b
)
SELECT
    row_number() OVER (),
    *
FROM
    cte
ORDER BY
    name

CodePudding user response：

I think a SQL UNION and the CONCAT() function should help you out here.

SELECT table1.id, CONCAT('t1-',table1.name) as name, table1.email
FROM table1
UNION ALL
SELECT table2.id, CONCAT('t2-',table2.name) as name, table2.email
FROM table1