home *** CD-ROM | disk | FTP | other *** search
- Xref: sparky comp.databases:7826 comp.databases.theory:609
- Newsgroups: comp.databases,comp.databases.theory
- Path: sparky!uunet!snorkelwacker.mit.edu!bloom-picayune.mit.edu!ilp.mit.edu!gold
- From: gold@ilp.mit.edu
- Subject: Simple SQL Join question
- Message-ID: <1992Nov13.152835.1@ilp.mit.edu>
- Sender: news@athena.mit.edu (News system)
- Nntp-Posting-Host: ilp.mit.edu
- Organization: MIT Industrial Liaison Program
- Date: Fri, 13 Nov 1992 20:28:35 GMT
- Lines: 51
-
- Hi!
- I have a basic SQL question regarding joins. We are querying
- several tables in a database for our 2000+ employees. A typical
- query would ask for:
-
- "List employee names and ids and each employee's
- department(s), publication(s), project(s),
- expertise keyword(s)
- for xxx number of employees."
-
- where "xxx" is anywhere from 1 to 2500 employees select by the user.
-
- Some employees have 10+ expertise keywords, 2-3 departments, 1-30
- projects, 1-500 publications.
-
- The SELECT statement that I am using is listed below. The problem
- with the SELECT statement's resultant table is that it produces SO
- MANY rows since every change in dept, project, publication,
- or keyword for each employee produces a new row, repeating the
- non-changed information. I realize that the Cartesian product of all
- of these tables does produce a large amount of rows in the resultant
- table.
-
- Is there a simplier, or less resource-consuming way of using SQL to
- answer the user's question (in quotes above) rather than using the
- SELECT statement below. Thank you for any assistance!
-
- SELECT employee.employee_id, employee.employee_name,
- dept.dept_name, project.project_name,
- publication.publication_name, keyword.keyword_name
- FROM employee,
- employee_dept,
- dept,
- employee_project,
- project,
- employee_publication,
- publication,
- employee_expertise,
- expertise
- WHERE employee.employee_id = employee_dept.employee_id AND
- employee_dept.dept_id = dept.dept_id AND
- employee.employee_id = employee_project.employee_id AND
- employee_project.project_id = project.project_id AND
- employee.employee_id = employee_publication.employee_id AND
- employee_publication.publication_id = publication.publication_id AND
- employee.employee_id = employee_project.employee_id AND
- employee_expertise.expertise_id = expertise.expertise_id
- -----------------
- Mark D. Gold
- gold@ilp.mit.edu
- (617) 253-0430
-