Avatar of tddeffner
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.

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

Avatar of undefined
Last Comment
Mark Wills

8/22/2022 - Mon

Would you like to post the sql then?

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

Open in new window


Sorry it appears that the whole statement was put into one line .  BTW Payroll tbl is pr_master_ks.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

did you try a left join?
Mark Wills

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question