SELECT statement problem

adavis

I have a project that I'm working on and am having trouble figuring out how to build my SELECT statements. Here's some sample data and the report I want to build:

developmentPlan table
|-----------------------------------------------|
| name         | dept | development             |
|--------------|------|-------------------------|
| SMITH, JOHN  |   1  | Learn PHP and mySQL     |
| SMITH, JAMES |   1  | Learn Perl and Postgres |
| JONES, FRED  |   2  | Learn ASP and msSql     |
| SMITH, JOHN  |   2  | Learn Perl and Oracle   |
| DOE, JOHN    |   3  | Learn HTML              |
|-----------------------------------------------|

trainingRequest table
--------------------------------------------------------------
|               |      | cors/|                              |
| name          | dept | conf | cors/conf name               |
|---------------|------|------|------------------------------|
| SMITH, JOHN   |   1  | CORS | Beginning PHP                |
| SMITH, JOHN   |   1  | CORS | Intermediate PHP             |
| SMITH, JOHN   |   1  | CORS | Advanced PHP                 |
| SMITH, JOHN   |   1  | CORS | Introduction to mySQL        |
| SMITH, JOHN   |   1  | CONF | PHP Conference               |
| SMITH, JOHN   |   1  | CONF | mySQL Conference             |
| SMITH, JOHN   |   1  | CONF | Internet Security Conference |
| SMITH, JAMES  |   1  | CORS | Beginning Perl               |
| SMITH, JAMES  |   1  | CORS | Intermediate Perl            |
| SMITH, JAMES  |   1  | CORS | Advanced Perl                |
| SMITH, JAMES  |   1  | CORS | Introduction to Postgres     |
| SMITH, JAMES  |   1  | CONF | Perl Conference              |
| SMITH, JAMES  |   1  | CONF | Postgres Conference          |
| SMITH, JAMES  |   1  | CONF | Internet Security Conference |
| JONES, FRED   |   2  | CORS | Beginning ASP                |
| JONES, FRED   |   2  | CORS | Intermediate ASP             |
| JONES, FRED   |   2  | CORS | Advanced ASP                 |
| JONES, FRED   |   2  | CORS | Introduction to msSQL        |
| JONES, FRED   |   2  | CONF | ASP Conference               |
| JONES, FRED   |   2  | CONF | msSQL Conference             |
| JONES, FRED   |   2  | CONF | Internet Security Conference |
| SMITH, JOHN   |   2  | CORS | Beginning Perl               |
| SMITH, JOHN   |   2  | CORS | Intermediate Perl            |
| SMITH, JOHN   |   2  | CORS | Advanced Perl                |
| SMITH, JOHN   |   2  | CORS | Introduction to Oracle       |
| SMITH, JOHN   |   2  | CONF | Perl Conference              |
| SMITH, JOHN   |   2  | CONF | Oracle Conference            |
| SMITH, JOHN   |   2  | CONF | Internet Security Conference |
| JONES, GEORGE |   3  | CORS | Beginning PHP                |
| JONES, GEORGE |   3  | CORS | Intermediate PHP             |
| JONES, GEORGE |   3  | CORS | Advanced PHP                 |
| JONES, GEORGE |   3  | CORS | Introduction to Postgres     |
|------------------------------------------------------------|

Employee: SMITH, JOHN 
Department: 1
Development: Learn PHP and mySQL

Courses:
  Beginning PHP
  Intermediate PHP
  Advanced PHP
  Introduction to mySQL

Conferences:
  PHP Conference
  mySQL Conference
  Internet Security Conference

------------------------------------------------

Employee: SMITH, JAMES
Department: 1
Development: Learn Perl and Postgres

Courses:
  Beginning Perl
  Intermediate Perl
  Advanced Perl
  Introduction to Postgres

Conferences:
  Perl Conference
  Postgres Conference
  Internet Security Conference

------------------------------------------------

Employee: JONES, FRED
Department:2
Development: Learn ASP and msSql

Courses:
  Beginning ASP
  Intermediate ASP
  Advanced ASP
  Introduction to msSQL

Conferences:
  ASP Conference
  msSQL Conference
  Internet Security Conference

------------------------------------------------

Employee: SMITH, JOHN
Department:2
Development: Learn Perl and Oracle

Courses:
  Beginning Perl
  Intermediate Perl
  Advanced Perl
  Introduction to Oracle

Conferences:
  Perl Conference
  Oracle Conference
  Internet Security Conference

------------------------------------------------

Employee: DOE, JOHN
Department:3
Development: Learn HTML

Courses:
  No Courses Requested

Conferences:
  No Conferences Requested

------------------------------------------------

Employee: JONES, GEORGE
Department: 3
Development: No Development Plan entered

Courses:
  Beginning PHP
  Intermediate PHP
  Advanced PHP
  Introduction to Postgres 

Conferences:
  No Conferences Requested

------------------------------------------------

I want the user to be able to search on last name and/or department to narrow the results.

This report will be used by individual employees to get their training requests and by managers to get the training requests by each employee within their department.

As you see, I have 2 different employees named John Smith, one employee with no conferences or courses, and one employee with not development plan

It has been suggested that I have two different sql statements, one for development plan and one for training requests. I think this would work if I didn't have any employees with the same last name.

Also, this data is entered in the database randomly, i.e. all the John Smith, Dept 1's may not be together.

Any suggestions?

Wien

You need a key in your tables . Else how do you want to distinguish to employee that have the same name.

adavis

The keys are there on the live tables. I just didn't show them because they aren't relevant to this discussion. They are autonumber fields.

tomhath

I'd suggest adding a few more tables:

Person - id, name and department
Skill - PHP, HTML, etc
Course - id, related skill, and course name

Change departmentPlan to use the ID from Person and ID from Skill (have a single row for each skill). This is an intersection table between Person and Skill

Change trainingRequest to have the departmentPlan ID, and Course ID (intersection table between departmentPlan and Course).

That gets rid of all the redundant data you currently have and makes the queries straightforward joins.

Wien

well, you need at least a foreign key in the trainingRequest table to refer the developmentPlan table Id (which is the person id)

adavis

I do already have some of those tables, like for instance a department table and a course table. A skills table is not relevant because this tool serves a large number of employees from accountants to engineers to secretaries to maintenance workers. At this time, I do not have an employee table, but that is in the plans for the next iteration. What I am currently doing is converting flat files & Perl to mySQL tables and PHP, then fixing my reports to work with what's there as sort of a temporary fix. We moved from flat files because several of our reports were timing out due to the huge volume of input records. For the next iteration, we can wipe out all of the current data and start from scratch, but at this point I have almost 10,000 records and don't want to mess with doing major table changes at this time.