tddeffner
asked on
SQL Join Issue
on: I am having an issue with a SQL Statement. The statement does pull records but the join is not working right.
It repeats records from both tables, but the second table should only have 2.
Example
What its doing
1 1
1 2
2 1
2 2
3 1
3 2
4 1
4 2
What I want it to do:
1 1
2 2
3
4
It repeats records from both tables, but the second table should only have 2.
Example
What its doing
1 1
1 2
2 1
2 2
3 1
3 2
4 1
4 2
What I want it to do:
1 1
2 2
3
4
peter57r
Would you like to post the sql then?
ASKER
The Tables I am trying to join are the misc_Tbl and the payroll.
SELECT a.last_name as 'Last Name',PR.prmast_first_name AS 'First Name',a.last_4_ssn AS 'Last 4 SSN',a.birth_date AS 'Birth Date',PR.prmast_loc_no AS 'Store No.',q.test_description as 'Test Title', min(a.graded) AS Graded, sum(a.points) AS Points, sum(a.poss_points) AS 'Points Possible', round(((sum(a.points) * 1.0 / sum(a.poss_points)) * 100.0),0) AS Percentage,a.attempt As Attempt,q.req_activation as 'User Activation',q.passper as '% to Pass',q.attemptallowed as 'Attempts Allowed',a.manual_entry as 'Manual Entry', min(a.test_completed) as 'Test Date Taken', c.category_name as Category,m.misc_certification AS 'Cert. No.',m.misc_desc AS 'Cert. Title',m.misc_date AS 'Cert. Date Taken',m.misc_hours AS 'Cert. Hours',m.misc_PF AS 'Cert. PF',m.misc_Grade AS 'Cert. Grade' FROM PR_MASTER_KS PR RIGHT OUTER JOIN Answer_Tbl A ON(PR.PRMAST_DATE_BIRTH = a.Birth_Date) AND (a.Last_4_SSN = PR.PRMAST_KEY1_EMP_NO) AND(a.Last_Name = PR.PRMAST_LAST_NAME)INNER JOIN Quiz_TBL q ON (q.Test_No = A.Test_No)INNER JOIN Category_tbl c ON( c.Category_No = A.Category)LEFT OUTER JOIN Misc_tbl m ON(PR.PRMAST_LAST_NAME = m.Last_Name)AND(PR.PRMAST_key1_emp_no = m.Last_4_ssn)AND(PR.PRMAST_date_birth = m.birth_Date) WHERE a.user_div = 35 GROUP BY A.Test_No, A.Category, A.attempt,a.last_name, PR.prmast_first_name,a.last_4_ssn,a.birth_date, PR.prmast_loc_no, q.test_description,a.attempt, q.req_activation, q.passper, q.attemptallowed,a.manual_entry, c.category_name,m.misc_certification,m.misc_desc,m.misc_date,m.misc_hours,m.misc_pf,m.misc_grade
ASKER
Sorry it appears that the whole statement was put into one line . BTW Payroll tbl is pr_master_ks.
did you try a left join?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.