home *** CD-ROM | disk | FTP | other *** search
/ NetNews Usenet Archive 1992 #26 / NN_1992_26.iso / spool / comp / database / 7826 < prev    next >
Encoding:
Internet Message Format  |  1992-11-14  |  2.4 KB

  1. Xref: sparky comp.databases:7826 comp.databases.theory:609
  2. Newsgroups: comp.databases,comp.databases.theory
  3. Path: sparky!uunet!snorkelwacker.mit.edu!bloom-picayune.mit.edu!ilp.mit.edu!gold
  4. From: gold@ilp.mit.edu
  5. Subject: Simple SQL Join question
  6. Message-ID: <1992Nov13.152835.1@ilp.mit.edu>
  7. Sender: news@athena.mit.edu (News system)
  8. Nntp-Posting-Host: ilp.mit.edu
  9. Organization: MIT Industrial Liaison Program
  10. Date: Fri, 13 Nov 1992 20:28:35 GMT
  11. Lines: 51
  12.  
  13. Hi!
  14.     I have a basic SQL question regarding joins. We are querying 
  15. several tables in a database for our 2000+ employees.  A typical 
  16. query would ask for:
  17.  
  18.         "List employee names and ids and each employee's
  19.          department(s), publication(s), project(s),
  20.          expertise keyword(s)
  21.          for xxx number of employees."
  22.  
  23. where "xxx" is anywhere from 1 to 2500 employees select by the user.
  24.  
  25. Some employees have 10+ expertise keywords, 2-3 departments, 1-30 
  26. projects, 1-500 publications.
  27.  
  28. The SELECT statement that I am using is listed below.  The problem 
  29. with the SELECT statement's resultant table is that it produces SO 
  30. MANY rows since every change in dept, project, publication, 
  31. or keyword for each employee produces a new row, repeating the 
  32. non-changed information.  I realize that the Cartesian product of all 
  33. of these tables does produce a large amount of rows in the resultant 
  34. table.
  35.  
  36. Is there a simplier, or less resource-consuming way of using SQL to 
  37. answer the user's question (in quotes above) rather than using the 
  38. SELECT statement below.  Thank you for any assistance!
  39.  
  40. SELECT  employee.employee_id, employee.employee_name,
  41.         dept.dept_name, project.project_name,
  42.         publication.publication_name, keyword.keyword_name
  43.   FROM  employee,
  44.         employee_dept,
  45.         dept,
  46.         employee_project,
  47.         project,
  48.         employee_publication,
  49.         publication,
  50.         employee_expertise,
  51.         expertise
  52.   WHERE employee.employee_id = employee_dept.employee_id AND
  53.         employee_dept.dept_id = dept.dept_id AND
  54.         employee.employee_id = employee_project.employee_id AND
  55.         employee_project.project_id = project.project_id AND
  56.         employee.employee_id = employee_publication.employee_id AND
  57.         employee_publication.publication_id = publication.publication_id AND
  58.         employee.employee_id = employee_project.employee_id AND
  59.         employee_expertise.expertise_id = expertise.expertise_id
  60. -----------------
  61. Mark D. Gold
  62. gold@ilp.mit.edu
  63. (617) 253-0430
  64.