SQL Question

thenrich used Ask the Experts™
The enclosed SQL statement returns the correct rows except I have two rows for every one. So I have twice as many rows as I need. What am I missing?

"SELECT a.ASS_ID, projmatl.rowpointer, projmatl.proj_num, projmatl.item, projmatl.item_desc, projmatl.matl_qty, projmatl.seq, projmatl.task_num, a.QtyRequired, job.job, a.SheetNumber, a.rowpointer, a.notes " & _
                        "FROM projmatl LEFT OUTER JOIN  " & _
                        "job ON projmatl.proj_num = job.ord_num AND projmatl.item = job.item AND projmatl.task_num = job.ord_line AND projmatl.seq = job.ord_release LEFT OUTER JOIN " & _
                        "AGRAMaster.dbo.ACS_ControlMaster AS a ON a.rowpointer = projmatl.rowpointer INNER JOIN " & _
                        "AGRAMaster.dbo.ACS_ControlDetail AS b ON b.row_pointer = a.rowpointer AND b.loadnumber='" & "L-1" & "' AND b.ProjectID=a.ProjectID " & _
                        "WHERE " & _
                        "(projmatl.proj_num = @PARM0)  and (projmatl.matl_type = @PARM1)"
                        "Order by projmatl.seq, projmatl.item_desc"
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Those duplicate records are coming out of one to many relationships between those tables.
Use DISTINCT clause in your SELECT statement to fix it like



Seems to work

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial