The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Customized join output

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 :)

Comments

  • Jim_KnicelyJim_Knicely Administrator Administrator

    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)
    
  • Thank u Mr.Jim Knicely :)

  • marcothesanemarcothesane Employee Employee

    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
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.