Display table with subitems-CodePudding

I have these 2 tables:

create table employees
(
    id              bigint primary key,
    account_id      integer,
    first_name      varchar(150),
    last_name       varchar(150)
);

create table accounts
(
    id               bigint primary key,
    account_name     varchar(150) not null
);

INSERT INTO employees(id, account_id, first_name, last_name)
VALUES(1, 1, 'Donkey', 'Kong');

INSERT INTO employees(id, account_id, first_name, last_name)
VALUES(2, 2, 'Ray', 'Kurzweil');

INSERT INTO employees(id, account_id, first_name, last_name)
VALUES(32, 2, 'Ray2', 'Kurzweil2');

INSERT INTO employees(id, account_id, first_name, last_name)
VALUES(33, 2, 'Ray3', 'Kurzweil3');

INSERT INTO employees(id, account_id, first_name, last_name)
VALUES(3432, 3, 'Percy', 'Fawcett');

INSERT INTO accounts(id, account_name)
VALUES(1, 'DK Banana Account');

INSERT INTO accounts(id, account_name)
VALUES(2, 'Kurzweil''s invetions moneyz baby!');

INSERT INTO accounts(id, account_name)
VALUES(3, 'Amazonian Emergency Fund');

Full test:

As you can see I don't need to display 3 times parent id and full name. I only need it once. Is this output possible with with SQL?

CodePudding user response：

Using lag, both order by should be the same

SELECT a.id
  , case a.id 
      when lag(a.id) over(order by a.id, e.first_name, e.last_name) then '' else a.account_name end account_name
  , e.first_name, e.last_name
FROM employees as e
JOIN accounts as a on a.id = e.account_id
order by a.id, e.first_name, e.last_name

CodePudding user response：

in your data,values such as follows must be changed to :

Ray  -> Ray
Ray2 -> Ray
Ray3 -> Ray

use REGEXP_REPLACE(string_value,'[[:digit:]]','','g') to remove digits from your columns,then use Subquery and distinct as follows:

SELECT DISTINCT id,
                account_name,
                first_name,
                last_name
FROM  (SELECT a.id,
              a.account_name,
              Regexp_replace(e.first_name, '[[:digit:]]', '', 'g') AS first_name
              ,
              Regexp_replace(e.last_name, '[[:digit:]]', '', 'g')  AS last_name
       FROM   employees AS e
              JOIN accounts AS a
                ON a.id = e.account_id) e2