SQL Question

thenrich
thenrich used Ask the Experts™
on
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"
Comment
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
Commented:
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

SELECT DISTINCT a.ASS_ID, ...

Author

Commented:
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