Customized join output

ManasaP

Hi Everyone,

Example consider two tables Persons and Employee

Persons table sample:

Name,Address
1. john,new York
2. Diana,new jersey

Employee table sample:

Employee ID,Team
1. 123,developer
2. 568,tester

Required join Table output should be like

Name,Address,Employee ID,Team
1. john,new York,null,null
2. Diana,new jersey,null,null
3. null.null,123,developer
4. null,null,568,tester

I want above sample as output is there any way to solve this issue.help me please

Jim_Knicely

Possible solution:

dbadmin=> SELECT * FROM person;
 name  |  address
-------+------------
 john  | new York
 Diana | new jersey
(2 rows)

dbadmin=> SELECT * FROM employee;
 ID  |   team
-----+-----------
 123 | developer
 568 | tester
(2 rows)

dbadmin=> SELECT name "Name", address "Address", NULL "Employee ID", NULL "Team"
dbadmin->   FROM person
dbadmin->  UNION ALL
dbadmin-> SELECT NULL, NULL, ID, team
dbadmin->   FROM employee;
 Name  |  Address   | Employee ID |   Team
-------+------------+-------------+-----------
 john  | new York   |             |
 Diana | new jersey |             |
       |            |         123 | developer
       |            |         568 | tester
(4 rows)

ManasaP

Thank u Mr.Jim Knicely

marcothesane

This looks like a standard FULL OUTER JOIN requirement.

Let me see.

\pset null NULL
WITH -- your input ....
persons(fname,addr) AS (
          SELECT 'John','New York'
UNION ALL SELECT 'Diana','New Jersey'
)
,
employees(employee_id,team) AS (
          SELECT 123,'developer'
UNION ALL SELECT 568,'tester'
)
SELECT
  fname
, addr
, NULL::INT         AS employee_id
, NULL::VARCHAR(16) AS team
FROM persons
UNION ALL
SELECT
  NULL::VARCHAR(16) AS fname
, NULL::VARCHAR(16) AS addr
, employee_id
, team
FROM employees;

-- out Null display is "NULL".
-- out  fname |    addr    | employee_id |   team    
-- out -------+------------+-------------+-----------
-- out  John  | New York   |        NULL | NULL
-- out  Diana | New Jersey |        NULL | NULL
-- out  NULL  | NULL       |         123 | developer
-- out  NULL  | NULL       |         568 | tester
-- out (4 rows)
-- out 
-- out Time: First fetch (4 rows): 25.915 ms. All rows formatted: 26.015 ms