Обсуждение: Two Tables That Share Data?
I want to create a separate table in my database called 'dept' and basically identify each unique department in my company represented by numeric code for example: Code: CREATE TABLE dept ( id SERIAL PRIMARY KEY, name VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(50) UNIQUE NOT NULL ); So this table should look something like: Code: id | name | email ----+--------------------------+-------------------- 1 | Information Technology | it@myco.tld 2 | Configuration Management | cm@myco.tld 3 | Facility | facility@myco.tld 4 | Software Development | software@myco.tld 5 | Finance | finance@myco.tld 6 | Logistics | logistics@myco.tld 7 | Inventory | inventory@myco.tld (7 rows) Now I'm going to make a new table called 'employees' & there is going to a field called 'dept' which will have a value from the 'id' field in the 'dept' table. My question is how does one traditionally configure this in SQL? When I create my employee table, what data type do I use to create the 'dept' field? It will only be storing a low numerical value since I only have less than 20 physical dept records. Do I need to create a foreign key constraint against this? I'm expecting the data to look like this: Code: id | name | dept | email | hire ----+-------------+------+-----------------+------------ 1 | James Smith | 5 | jsmith@myco.tld | 2011-04-19 (1 row) As you can see the user James Smith is in dept. #5 which is my finance dept. Can someone please point me into the right direction on how to get this database working as mentioned above?
On 08/02/2012 20:34, Carlos Mennens wrote: > I want to create a separate table in my database called 'dept' and > basically identify each unique department in my company represented by > numeric code for example: > > Code: > CREATE TABLE dept > ( > id SERIAL PRIMARY KEY, > name VARCHAR(50) UNIQUE NOT NULL, > email VARCHAR(50) UNIQUE NOT NULL > ); > > So this table should look something like: > > Code: > id | name | email > ----+--------------------------+-------------------- > 1 | Information Technology | it@myco.tld > 2 | Configuration Management | cm@myco.tld > 3 | Facility | facility@myco.tld > 4 | Software Development | software@myco.tld > 5 | Finance | finance@myco.tld > 6 | Logistics | logistics@myco.tld > 7 | Inventory | inventory@myco.tld > (7 rows) > > Now I'm going to make a new table called 'employees'& there is going > to a field called 'dept' which will have a value from the 'id' field > in the 'dept' table. My question is how does one traditionally > configure this in SQL? > > When I create my employee table, what data type do I use to create the > 'dept' field? It will only be storing a low numerical value since I > only have less than 20 physical dept records. Do I need to create a > foreign key constraint against this? I'm expecting the data to look > like this: > > Code: > id | name | dept | email | hire > ----+-------------+------+-----------------+------------ > 1 | James Smith | 5 | jsmith@myco.tld | 2011-04-19 > (1 row) > > As you can see the user James Smith is in dept. #5 which is my finance > dept. Can someone please point me into the right direction on how to > get this database working as mentioned above? > You should keep the data types the same. SERIAL is in fact INTEGER so dept should be INTEGER. You do not need the foreign key, but it's a good idea. Foreign keys add a little overhead as they have to be checked when you insert rows. However foreign keys are a good idea. The provide a lock against bad data being entered into the database (employees in a department that doesn't exist or a department being deleted while it still has employees).
On Wed, Feb 8, 2012 at 4:06 PM, Philip Couling <phil@pedal.me.uk> wrote: > You should keep the data types the same. SERIAL is in fact INTEGER so dept > should be INTEGER. OK I have my 'employees.dept' field type set to INTEGER but I'm still trying to understand how I can perform a SQL statement that will allow me to query the 'employees' table and visibly see employees.manager = 'Phill Collins' rather than it's assigned numerical INTEGER. Below are both tables described: Table "public.managers" Column | Type | Modifiers --------+-----------------------+------------------------------------------------------- id | integer | not null default nextval('managers_id_seq'::regclass) name | character varying(50) | not null email | character varying(50) | not null dept | integer | salary | numeric(8,2) | not null hire | date | not null Indexes: "managers_pkey" PRIMARY KEY, btree (id) "managers_email_key" UNIQUE CONSTRAINT, btree (email) Table "public.employees" Column | Type | Modifiers ---------+-----------------------+-------------------------------------------------------- id | integer | not null default nextval('employees_id_seq'::regclass) fname | character varying(50) | not null lname | character varying(50) | not null email | character varying(50) | not null dept | integer | manager | integer | salary | numeric(8,2) | not null hire | date | not null Indexes: "employees_pkey" PRIMARY KEY, btree (id) "employees_email_key" UNIQUE CONSTRAINT, btree (email) Is there a way I can query the employees table and have the SQL statement resolve the INTEGER value from 'employees.manager' to display the data in 'managers.name'?
On 9 February 2012 17:33, Carlos Mennens <carlos.mennens@gmail.com> wrote: > OK I have my 'employees.dept' field type set to INTEGER but I'm still > trying to understand how I can perform a SQL statement that will allow > me to query the 'employees' table and visibly see employees.manager = > 'Phill Collins' rather than it's assigned numerical INTEGER. Below are > both tables described: Try something like this: SELECT e.fname AS emp_fname, e.lname AS emp_lname, m.name AS manager FROM employees AS e INNER JOIN managers AS m ON e.manager = m.id WHERE e.salary = 12345.67; -- Michael Wood <esiotrot@gmail.com>
On Thu, Feb 9, 2012 at 11:28 AM, Michael Wood <esiotrot@gmail.com> wrote: > Try something like this: > > SELECT e.fname AS emp_fname, e.lname AS emp_lname, m.name AS manager > FROM employees AS e > INNER JOIN managers AS m ON e.manager = m.id > WHERE e.salary = 12345.67; I'm going to try this now but while I was working on this in pgAdmin3 query builder tool, I got the results I wanted which was to just query my employee table and translate the employees.manager field from INTEGER to their actual name referenced in 'managers.name' as show below: psql (9.1.2) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. zoo=# SELECT employees.id, employees.fname, employees.lname, managers.name AS manager FROM employees, managers WHERE employees.manager = managers.id AND employees.fname = 'Carlos'; id | fname | lname | manager ----+--------+---------+--------------- 1 | Carlos | Mennens | Phill Collins (1 row)
Hi,
Regards,
this is different (old) SQL dialect, where WHERE condition is similar to INNER JOIN.
There is nothing magic in translation. In Your example employees.manager = managers.id act the same role as Michael's INNER JOIN managers AS m ON e.manager = m.id , rest of Your WHERE is simple condition to find employee 'Carlos' and related manager - relation is because previous WHERE condition exists.
Please notice, that displayed columns depend on SELECT statement, I mean SELECT and list of columns You want to display.
Regards,
Bartek
2012/2/9 Carlos Mennens <carlos.mennens@gmail.com>
On Thu, Feb 9, 2012 at 11:28 AM, Michael Wood <esiotrot@gmail.com> wrote:I'm going to try this now but while I was working on this in pgAdmin3
> Try something like this:
>
> SELECT e.fname AS emp_fname, e.lname AS emp_lname, m.name AS manager
> FROM employees AS e
> INNER JOIN managers AS m ON e.manager = m.id
> WHERE e.salary = 12345.67;
query builder tool, I got the results I wanted which was to just query
my employee table and translate the employees.manager field from
INTEGER to their actual name referenced in 'managers.name' as show
below:
psql (9.1.2)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
zoo=# SELECT
employees.id,
employees.fname,
employees.lname,
managers.name AS manager
FROM
employees,
managers
WHERE
employees.manager = managers.id AND employees.fname = 'Carlos';
id | fname | lname | manager
----+--------+---------+---------------
1 | Carlos | Mennens | Phill Collins
(1 row)
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice