Display and sort data from 2 tables

by

The question:

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?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

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.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment