2h2h2h 2h2h2h - 16 days ago 4
SQL Question

I keep getting duplicate data in my record

I am using MS access.

I tried write SQL code that refer to the tables EMPLOYEE, PROJECT and JOB and return the results as shown in the figure below:

PROJ_NAME PROJ_VALUE PROJ_BALANCE EMP-LNAME EMP_FNAME EMP_INITIAL JOB_CODE JOB_DESCRIPTION JOB_CHG_HOUR
iCEBUTT 800.00 12000 SMITH ANNE E 12 ELECTRIC ENGENEER 10
FIREBUTT 810.00 20000 SHEMAN ANNE G 15 WATER ENGENEER 12
iCEBTEA 802.00 10000 SIMPSON ANNE H 11 NON ENGENEER 11
iCECUBE 890.00 18000 SMITFIELD ANNE A 19 ELECTRIC ENGENEER 9.5


MY CODE

SELECT PROJ_NAME, PROJ_VALUE, PROJ_BALANCE, EMP_LNAME, EMP_FNAME, EMP_INITIAL, JOB_CODE, JOB_DESCRIPTION, JOB_CHG_HOUR
FROM EMPLOYEE, JOB, PROJECT
WHERE PROJ_VALUE >= 10000;


what I got from my code is a lot of duplicate datas.
My result:

PROJ_NAME PROJ_VALUE PROJ_BALANCE EMP-LNAME EMP_FNAME EMP_INITIAL JOB_CODE JOB_DESCRIPTION JOB_CHG_HOUR
iCEBUTT 800.00 12000 SMITH ANNE E 12 ELECTRIC ENGENEER 10
iCEBUTT 800.00 12000 SMITH ANNE E 12 ELECTRIC ENGENEER 10
iCEBUTT 800.00 12000 SMITH ANNE E 12 ELECTRIC ENGENEER 10
iCEBUTT 800.00 12000 SMITH ANNE E 12 ELECTRIC ENGENEER 10
FIREBUTT 810.00 20000 SHEMAN ANNE G 15 WATER ENGENEER 12
FIREBUTT 810.00 20000 SHEMAN ANNE G 15 WATER ENGENEER 12
FIREBUTT 810.00 20000 SHEMAN ANNE G 15 WATER ENGENEER 12
FIREBUTT 810.00 20000 SHEMAN ANNE G 15 WATER ENGENEER 12
iCEBTEA 802.00 10000 SIMPSON ANNE H 11 NON ENGENEER 11
iCEBTEA 802.00 10000 SIMPSON ANNE H 11 NON ENGENEER 11
iCEBTEA 802.00 10000 SIMPSON ANNE H 11 NON ENGENEER 11
iCECUBE 890.00 18000 SMITFIELD ANNE A 19 ELECTRIC ENGENEER 9.5
iCECUBE 890.00 18000 SMITFIELD ANNE A 19 ELECTRIC ENGENEER 9.5
iCECUBE 890.00 18000 SMITFIELD ANNE A 19 ELECTRIC ENGENEER 9.5

Answer

You are missing the condition for the joins. Now both employee and job give all records. You have to tell how they relate to the project table.

Something like this:

SELECT  p.PROJ_NAME, p.PROJ_VALUE, p.PROJ_BALANCE, p.EMP_LNAME, p.EMP_FNAME, p.EMP_INITIAL,            p.JOB_CODE, p.JOB_DESCRIPTION, p.JOB_CHG_HOUR
FROM EMPLOYEE e, JOB j, PROJECT p
WHERE p.job_code = j.job_code /*enter correct fields here*/
AND   p.emp_name = e.emp_name /*enter correct fields here*/
AND   p.PROJ_VALUE >= 805000.00;

The field names I used are based on your current query. I hope there are some ID fields you could use instead.

Preferably use real joins:

SELECT  p.PROJ_NAME, p.PROJ_VALUE, p.PROJ_BALANCE, p.EMP_LNAME, p.EMP_FNAME, p.EMP_INITIAL,            p.JOB_CODE, p.JOB_DESCRIPTION, p.JOB_CHG_HOUR
FROM PROJECT p
JOIN JOB j
ON   p.job_code = j.job_code /*enter correct fields here*/
JOIN EMPLOYEE e
ON   p.emp_name = e.emp_name /*enter correct fields here*/
WHERE p.PROJ_VALUE >= 805000.00;