Display and sort data from 2 tables



  • 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?



  • 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.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2