-1

I have created four tables (company1, company2, company3, company4) with column names the same in all tables: empid, org, firstname, lastname. All four tables have the primary key as empid. I want to create a view which should bring all data from the four tables which should be groupedby 'org' and orderedby 'empid'. I tried using union, union all, join.

Please suggest how to do the table creation or view creation as it is going to maintain the database for long term use as an organisational database.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Kiran K
  • 13
  • 4
  • 1
    Grouping by org implies that you will be aggregating the data, and unless you also group by emp_id you can't order by it. Please show sample input data and output data – David Aldridge Jan 25 '17 at 20:06
  • 2
    What is the design decision that came up with 4 tables for companies, as opposed to a single table? – BobC Jan 25 '17 at 20:10
  • 1
    What do you need to show in the view? Also, when you say "grouped" by `ORG`, do you actually mean "ordered" by `ORG`, and then within each `ORG`, sub-ordered by `EMPID`? If so, UNION ALL is the right operator to use; and `ORDER BY ORG, EMPID`. Of course, you can't order by `EMPID` if you **group** by `ORG`, as David has said already. –  Jan 25 '17 at 20:43
  • @BobC, Thanks for response, but we have different sub branches which have maintain its database in their own table say branch1 has database table table1, branch2 has database table table2 and so on. – Kiran K Jan 26 '17 at 09:02
  • the view which i am thinking should be,say empid 1,2,3... and org 'hp' and empid 4,5,6 org 'dell, etc... the thing is i want a view which should have data from all the tables which is organised by org name and as per org it should be in sorted by by empid, i dont want whole data to be sorted by by empid. it should be first 'org' and within org 'empid'. – Kiran K Jan 26 '17 at 09:12
  • @GaneshKandekar. So what happens when you get a fifth branch? You create new table? – BobC Jan 26 '17 at 16:15
  • Please show your table definitions and example data. Don't clarify in comments, edit your question. You aren't being clear about what you want output. – philipxy Jan 27 '17 at 07:17

1 Answers1

0

do you mean something like

create or replace view all_companies
as
select 'company1' company_name,
        empid,
        org,
        firstname,
        lastname
from company1
UNION ALL
select 'company2' company_name,
        empid,
        org,
        firstname,
        lastname
from company2
UNION ALL
select 'company3' company_name,
        empid,
        org,
        firstname,
        lastname
from company3
UNION ALL
select 'company4' company_name,
        empid,
        org,
        firstname,
        lastname
from company4
order by org,empid
;
ShoeLace
  • 3,476
  • 2
  • 30
  • 44
  • @ShoeLance Can i use order by 'empid' as it is primary key in each table. and what if i use group by "org" and then order by "empid".and how to do this. – Kiran K Jan 26 '17 at 14:04
  • 2
    adding the order by in the view is compleytely redundent in anycase.. you really need to specify teh order when you select from eth view.. ie `select * from all_companies order by company_name,empid` – ShoeLace Jan 26 '17 at 14:26
  • @GaneshKandekar It doesn't matter what the PK is and you don't want GROUP BY. You are thinking of how the output is "grouped" but that follows from the ORDER BY. If you ORDER BY org then empid then all of one org's empids will be together. Use SELECT to put the columns in the order you want. – philipxy Jan 27 '17 at 07:17
  • Why not ORDER BY org, empid? Also, integers in the ORDER BY refer to the columns of the SELECT, so your 1 & 2 are company_name, empid not org, empid. – philipxy Jan 27 '17 at 07:26
  • @ShoeLace,@philipxy thanks for your suggestion,and its worked for me with ORDER BY org,empid. – Kiran K Jan 28 '17 at 15:27
  • ok..@GaneshKandekar i've updated the answer with your desired order by clause.. please mark is is correct if you are pleased. – ShoeLace Jan 30 '17 at 10:24