SELECT statement help

Codewalkers Forums Sponsor:

December 3rd, 2002, 01:34 PM

casa

Join Date: Apr 2007

Posts: 20

Time spent in forums: < 1 sec
Reputation Power: 0

SELECT statement help

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:

php Code:

Original - php Code

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

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

 
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?

December 4th, 2002, 07:22 PM

bob0099

Join Date: Apr 2007

Location: manchester,n.h.,usa

Posts: 172

Time spent in forums: < 1 sec
Reputation Power: 2

RE: SELECT statement help

i would suggest creating an employee id number for each employee to minimize problems. this way you could select by id # and not worry about names.

December 4th, 2002, 08:12 PM

casa

Join Date: Apr 2007

Posts: 20

Time spent in forums: < 1 sec
Reputation Power: 0

RE: SELECT statement help

I'm converting a Perl program which used flat files to PHP/mySQL because some of the reports kept timing out. I have almost 7,000 records in the trainingRequest table, so would be a major ordeal to add user id at this time. Actually there is a userid, but I'm not using it yet.

Looking back at my Perl code, I created had a array with a slot for all the needed fields from each table. I pushed all the idp records that met my filter criteria into this array, then pushed the training request records that met my filter criteria into the same array. After I got the records run through the filters and pushed into the array, I sorted it. The first few fields looked like this:

department|employee name|A(for idp),CORS(for course),CONF(for conference)|course name,conference name,null if idp|etc...

So my array would have all the departments together, then all the employees sorted within that department, then the IDP information first, then all the conferenced they wanted , then all the course they wanted. So then I just plucked them off and started a new section when ever the name changed.

It's just that with so many records, if someone requests a report on all the employees in all the departments in the whole company (which is an option), it might time out.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: