CODEWITHSUNDEEP
sqlinsertsybaseunion
DSD
DSD

Reputation: 601

Sybase, create a new table from a select statment from multiple tables

I have two tables in Sybase:

TABLE1: holds the department data and emp_id associated with departments:

Dept_Id     Emp_id1   Emp_id2   Emp_id3
-------     -------   -------   -------
DEP1          11        22        33

TABLE2: holds the Emp_Id and multiple Address_Id associated with employee:

Emp_Id     Add_Id
------    --------
11         street1
11         street2
11         street3
22         mountRoad1
22         mountRoad2
33         PetersRoad

I want to join these two tables and insert the following results into a new table:

Dept_Id   Emp_Id   Add_Id
-------   ------   ------
DEP1       11       street1
DEP1       11       street2
DEP1       11       street3
DEP1       22       mountRoad1
DEP1       22       mountRoad2
DEP1       33       PetersRoad

How can I achieve this using single SQL query?

Upvotes: 1

Views: 5379

Answers (3)

www
www

Reputation: 4391

Why not use JOIN only:

insert into table3 (Dep_id,Emp_id, Add_id)
select table1.Dep_id, table2.Emp_id, Table2.Add_id
from table1 
join table2 on table1.Emp_id1=table2.Emp_id or table1.Emp_id2=table2.Emp_id or table1.Emp_id3=table2.Emp_id

Upvotes: 3

Chris Moutray
Chris Moutray

Reputation: 18369

If you're stuck with the silly structure for table1 then I'd probably create a view to artificially change the structure to a simple department, employee table. Something like this:

create view EmployeeDepartment
as 
    select Dept_Id, Emp_id1 as Emp_id from Table1
    union select Dept_Id, Emp_id2 as Emp_id from Table1
    union select Dept_Id, Emp_id3 as Emp_id from Table1

You can then use this view as required eg to select out department/employee/address

select e.Dept_Id, e.Emp_Id, a.Add_Id
from Table_2 a
join EmployeeDepartment e on e.Emp_id = a.Emp_id

Or to build your 3rd table

insert into table3 (Dep_id, Emp_id, Add_id)
select e.Dept_Id, e.Emp_Id, a.Add_Id
from Table_2 a
join EmployeeDepartment e on e.Emp_id = a.Emp_id

On the other hand its just as easy to sub query the views select to build your 3rd table

insert into table3 (Dep_id, Emp_id, Add_id)
select e.Dept_Id, e.Emp_Id, a.Add_Id
from Table_2 a
join (
    select Dept_Id, Emp_id1 as Emp_id from Table1
    union select Dept_Id, Emp_id2 as Emp_id from Table1
    union select Dept_Id, Emp_id3 as Emp_id from Table1) e 
on e.Emp_id = a.Emp_id

Certainly the view is more reusable if you have other scenarios and easier to maintain if you end up adding a 4th employee id (emp_id4). Other queries using the view wont need to change...

Upvotes: 0

valex
valex

Reputation: 24144

I think you should use UNION. For example:

Insert into Table 3 (Dep_id,Emp_id, Add_id)
select Dep_id,Emp_id1 as Emp_id, Table2.Add_id
from Table 1 
join Table2 on (Table1.Emp_id1=Table2.Emp_id)
union all
select Dep_id,Emp_id2 as Emp_id, Table2.Add_id
from Table 1 
join Table2 on (Table1.Emp_id2=Table2.Emp_id)
union all
select Dep_id,Emp_id3 as Emp_id, Table2.Add_id
from Table 1 
join Table2 on (Table1.Emp_id3=Table2.Emp_id)

PS: what if you need to add Emp_id4???? Your table structure is not normalized.

Upvotes: 0

Related Questions