-1

I have these test tables which I would like to select and combine the result:

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

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

create table short_name
(
    account_id       bigint primary key,
    full_name     varchar(150) not null
);

INSERT INTO short_name(account_id, full_name)
VALUES(1, 'city 1');

INSERT INTO short_name(account_id, full_name)
VALUES(2, 'city 2');

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

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

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

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

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

INSERT INTO accounts(id, account_name, timestamp)
VALUES(1, 'DK Banana Account', '5-10-10');

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

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

I tried this:

select * from employees as e
    INNER JOIN short_name as sn on sn.account_id = a.id
union
select * from accounts as a;

https://www.db-fiddle.com/f/pwzwQTsHuP27UDF17eAQy4/29

How I can select the tables and display a combined table rows ordered by timestamp? Is it possible to display also the name of the tables as a first result column?

3
  • I juts want to print the result form tables. Commented Apr 21, 2022 at 12:25
  • no, I want to combine the tables - example: if I have 2 tables with 3 rows into the first one second table with 4 rows at the end I wan to have a table with 7 rows. Commented Apr 21, 2022 at 12:57
  • You'll have to better explain your requirements - you can't UNION two relations with different numbers of fields. Is something like this what you're looking for?
    – Vérace
    Commented Apr 21, 2022 at 14:54

1 Answer 1

1
+50

As the comments suggest, your requirements are not quite clear.

It sounds like you just want to output both tables, however, if you do the following:

select e.*, a.* from employees e, accounts a;

You just build the cartesian product, meaning you combine every row from employees with every row from accounts (3 rows in accounts * 5 rows in employees = 15 new rows).

Now you have an account_id row in your employee table, meaning that you can join it on this, to match every employee with an account:

select e.*, a.* from employees e, accounts a
where e.account_id=a.id;

This results in the 5 rows from employee, plus the account information from the accounts table matched to each employee. You can now also add the information from the short_name table like this:

select e.*, a.*,sn.* from employees e, accounts a, short_name sn
where e.account_id=a.id
and sn.account_id=a.id;

This results in 4 rows since the accounts with account_id=3 have no matching entry short_name. If you want to see all 5 again you have to do a left join:

select e.*, a.*,sn.* from employees e, accounts a
left join short_name sn on sn.account_id=a.id
where e.account_id=a.id;

Now, the missing fields for account_id=3 will just be 'null'.

In this case, it would also be best practice to move the full_name column from the short_name table to the accounts table, since it is a 1:1 relationship with information regarding the account.

Guessing that the entries in the short_name table regard cities, and you want each city entry only once, you would do it the other way around: Add a city_id column on the accounts table and use that to identify the city in the short_name table, thus resulting in a n:1 relation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.