Thread: JOINING TWO TABLES

Hi ,
      I have two tables
First table is employee table

EMP_ID Ename     MGR_ID
----------   --          ----------
       1     a           1
       2     b           1
       3     c           2


Second one is manager table

MGR_ID Manager name
----------    --------------------
       1     d
       2      e


as managers are employees i joined those two tables
the result is

EMP_ID EM     MGR_ID
----------   --         ----------
       4    d
       5    e
       1    a           1
       2    b           1
       3    c           2


now i would like to give emp ids to mgr ids.

I mean d,e got 1 and 2 in mgr table.
d,e got 4,5 in new table.

I would like to replace 1 1 2 with 4 4 5.

I would like to modify above table as


EMP_ID EM     MGR_ID
----------   --         ----------
       4    d
       5    e
       1    a           4
       2    b           4
       3    c           5


Plz help me
Thanks
satya

--- vamseelist@gmail.com wrote:
> I mean d,e got 1 and 2 in mgr table.
> d,e got 4,5 in new table.
>
> I would like to replace 1 1 2 with 4 4 5.
>
> I would like to modify above table as

I would modify your manager and employees tables to look like:


CREATE TABLE Employees (
  emp_id SERIAL PRIMARY KEY,
  name text,
  mgr_id INTEGER);

CREATE TABLE Managers (
  mgr_id INTEGER PRIMARY KEY REFERENCES Employees( emp_id ));

   ALTER TABLE Employees
ADD CONSTRAINT managers_of_employees
   FOREIGN KEY ( mgr_id )
    REFERENCES Managers( mgr_id );

Regards,
Richard Broersma Jr.

Even you can drop the manager table and only keep employees table containg
mangers and employees to and then self join the table.
and then query


select e.employee_name , e1.employee_name as manager_name from
employee e, employee e1
where e.manager_id=e1.employee_id

With Regards
Ashish


----- Original Message -----
From: "Richard Broersma Jr" <rabroersma@yahoo.com>
To: <vamseelist@gmail.com>; <pgsql-novice@postgresql.org>
Sent: Monday, August 06, 2007 9:57 PM
Subject: Re: [NOVICE] JOINING TWO TABLES


>
> --- vamseelist@gmail.com wrote:
>> I mean d,e got 1 and 2 in mgr table.
>> d,e got 4,5 in new table.
>>
>> I would like to replace 1 1 2 with 4 4 5.
>>
>> I would like to modify above table as
>
> I would modify your manager and employees tables to look like:
>
>
> CREATE TABLE Employees (
>  emp_id SERIAL PRIMARY KEY,
>  name text,
>  mgr_id INTEGER);
>
> CREATE TABLE Managers (
>  mgr_id INTEGER PRIMARY KEY REFERENCES Employees( emp_id ));
>
>   ALTER TABLE Employees
> ADD CONSTRAINT managers_of_employees
>   FOREIGN KEY ( mgr_id )
>    REFERENCES Managers( mgr_id );
>
> Regards,
> Richard Broersma Jr.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org