|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
August 8th, 2004, 05:55 PM
|
|||
|
|||
Difficult question involving 3 tables
Curently doing an assignment and I'm stuck on one question....DESPERATELY NEED HELP!
Display alphabetically the name of each employee who has worked on a project whose project number is to be input at runtime and the total amount charged by each employee to the project. In other words the desiered output should look like this: ENAME FNAME PROJECT_NO TOTAL CHARGE Smith Jane 1000 800.67 Smith Jane 1102 1232323.46 Vik John 1233 1234568.67 Von Vikki 1102 3343.76 Rather my output looks like Smith jane 1102 456.45 Smith jane 1102 343.67 Smith jane 1102 1232.45 Etc… Vik john 1233 545.43 Vik john 1233 745.34 Etc… So my question is how do code it to only output one row for each employee…Based on the employees name and project number The 3 tables I have to use are listed below Table: Employee Name Null? Type ----------------------------------------- -------- ------------ EMP_NO NOT NULL NUMBER(38) ENAME NOT NULL VARCHAR2(20) FNAME NOT NULL VARCHAR2(20) JOB NOT NULL VARCHAR2(15) MGR NUMBER(38) HIREDATE NOT NULL DATE SAL NOT NULL NUMBER(7,2) COMM NUMBER(7,2) DEPT_NO NOT NULL NUMBER(38) Table: Department Name Null? Type ----------------------------------------- -------- ------------ DEPT_NO NOT NULL NUMBER(38) DEPT_NAME NOT NULL VARCHAR2(15) LOCN NOT NULL VARCHAR2(15) Table: Project_hour Name Null? Type ----------------------------------------- -------- ----------- PROJECT_NO NOT NULL FLOAT(126) EMP_NO NOT NULL NUMBER(38) TIME_LOG_DATE NOT NULL DATE HOURS_LOGGED NOT NULL FLOAT(126) DOLLARS_CHARGED NOT NULL FLOAT(126) MY CODE: select e.ename, fname, p.doll, b.project_no from employee e, (select sum(dollars_charged) doll from project_hour group by emp_no) p, (select project_no from project_hour where emp_no= emP_no group by project_no, emp_no) b order by ename, fname Any help would be greatly appreaciated! thanks
|
|
#2
August 9th, 2004, 02:40 PM
|
|||
|
|||
|
Well it took me a while but I finally figured it out!
 select p.project_no "Project #", e.ename "Name", sum(dollars_charged) "Total Charge" from project_hour p, employee e where p.emp_no = e. emp_no group by p.project_no, e.ename order by e.ename;
|
|
| Viewing: ASP Free Forums > Database > SQL Development > Difficult question involving 3 tables |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
