Link to home
Start Free TrialLog in
Avatar of advantagec
advantagec

asked on

Remove duplicate results from MS SQL Query when using Joins.

What I need to do is take the following information and only select distinct values for oe_line.customer_part_number.

My Query in MS SQL is as follows. I've also included a "WHERE" clause for diagnostic purposes:

SELECT oe_line.customer_part_number AS 'Customer Part Number [1]', 
	invoice_hdr.po_no AS 'PO Number [2]', 
	oe_line.qty_allocated AS 'Qty Allocated [3]', 
	oe_line.line_no AS 'PO Line Number [4]', 
	invoice_line.item_id AS "Item ID / Serial # [5]", 
	invoice_line.item_desc AS "Description [6]"
	
	FROM [P21].[dbo].[oe_line]
	RIGHT OUTER JOIN [P21].[dbo].[invoice_line]
	ON oe_line.order_no = invoice_line.order_no
	RIGHT OUTER JOIN [P21].[dbo].[invoice_hdr]
	ON oe_line.order_no = invoice_hdr.order_no
	WHERE oe_line.order_no = '1003227'

Open in new window


The results obtained are as follows, truncated to give a basic idea of what is happening:

157828-1,LLSS07454,0.000000000,1,DC-750-35,Quick Coupler Check Valve Assys.
2008153,LLSS07454,2.000000000,2,DC-750-35,Quick Coupler Check Valve Assys.
B511BDB48C,LLSS07454,12.000000000,3,DC-750-35,Quick Coupler Check Valve Assys.
171902-1,LLSS07454,2.000000000,4,DC-750-35,Quick Coupler Check Valve Assys.
253443-1,LLSS07454,14.000000000,5,DC-750-35,Quick Coupler Check Valve Assys.
253444-1,LLSS07454,2.000000000,6,DC-750-35,Quick Coupler Check Valve Assys.
BM-38,LLSS07454,4.000000000,7,DC-750-35,Quick Coupler Check Valve Assys.
219P-4,LLSS07454,14.000000000,8,DC-750-35,Quick Coupler Check Valve Assys.
219P-6,LLSS07454,6.000000000,9,DC-750-35,Quick Coupler Check Valve Assys.
HARSCO AIR VALVE ASSYS.,LLSS07454,18.000000000,10,DC-750-35,Quick Coupler Check Valve Assys.
2013625,LLSS07454,0.000000000,11,DC-750-35,Quick Coupler Check Valve Assys.
250975-1,LLSS07454,0.000000000,12,DC-750-35,Quick Coupler Check Valve Assys.
157828-1,LLSS07454,0.000000000,1,2013625,P1M DUPLEX CYL.
2008153,LLSS07454,2.000000000,2,2013625,P1M DUPLEX CYL.
B511BDB48C,LLSS07454,12.000000000,3,2013625,P1M DUPLEX CYL.
171902-1,LLSS07454,2.000000000,4,2013625,P1M DUPLEX CYL.
253443-1,LLSS07454,14.000000000,5,2013625,P1M DUPLEX CYL.
253444-1,LLSS07454,2.000000000,6,2013625,P1M DUPLEX CYL.
BM-38,LLSS07454,4.000000000,7,2013625,P1M DUPLEX CYL.
219P-4,LLSS07454,14.000000000,8,2013625,P1M DUPLEX CYL.
219P-6,LLSS07454,6.000000000,9,2013625,P1M DUPLEX CYL.
HARSCO AIR VALVE ASSYS.,LLSS07454,18.000000000,10,2013625,P1M DUPLEX CYL.
2013625,LLSS07454,0.000000000,11,2013625,P1M DUPLEX CYL.
250975-1,LLSS07454,0.000000000,12,2013625,P1M DUPLEX CYL.

Open in new window



I tried grouping these results as I would if I were using MySQL and apparently MS SQL forces you to aggregate columns so this is not a good solution.
Avatar of didnthaveaname
didnthaveaname

I'm a little confused by what you want to accomplish.  Can you maybe modify the output to show what you're looking for?  You only have to use aggregate functions for what is not specified in the group by clause (the set of attributes that you use to define your grouping set)
Avatar of Surendra Nath
I am not sure what you are after, but if you need only distinct customer part number then the below query will help

SELECT distinct oe_line.customer_part_number AS 'Customer Part Number [1]', 
	FROM [P21].[dbo].[oe_line]
	RIGHT OUTER JOIN [P21].[dbo].[invoice_line]
	ON oe_line.order_no = invoice_line.order_no
	RIGHT OUTER JOIN [P21].[dbo].[invoice_hdr]
	ON oe_line.order_no = invoice_hdr.order_no
	WHERE oe_line.order_no = '1003227'

Open in new window

Avatar of advantagec

ASKER

Neo_jarvis, I need all the other data that goes along with that Distinct customer part number.

Here is the output to your query :
157828-1
171902-1
2008153
2013625
219P-4
219P-6
250975-1
253443-1
253444-1
B511BDB48C
BM-38
HARSCO AIR VALVE ASSYS.

Open in new window

I need 12 total results with those distinct part numbers.
I understand what you're wanting now, but I'm not sure why the aggregate functions were causing an issue.  With the additional information, all of those results are not distinct, so the distinct clause won't filter them.  You can further define the granularity of your grouping clause to get what you want, though.
It appears that all of your selected columns have distinct values except for the item_description. If you want to only have one customer_part_number per line, then you need to establish the criteria to determine (e.g. min or max) which item_description you want.
The expected output is:

Distinct:
[dbo].[invoice_line].[line_no] = PO Line Number [4]

Distinct, based on line_no:
[dbo].[invoice_line].[customer_part_number] = Customer Part Number [1]
[dbo].[invoice_line].[qty_shipped] = Qty Shipped / Allocated [3]
[dbo].[invoice_line].[item_id] = Item ID / Serial Number [5]
[dbo].[invoice_line].[item_desc] = Description [6]

Same across results:
[dbo].[invoice_hdr].[po_no] = PO Number [2]
hello there,

I understood your problem, but still I need some more clarity.

what the result should be for the part number 253443-1 ???
253443-1,LLSS07454,14.000000000,5,2013625,P1M DUPLEX CYL.
253444-1,LLSS07454,2.000000000,6,2013625,P1M DUPLEX CYL.

Looking at your code, I guess this should be as below, the sum of the quantity,
253443-1,LLSS07454,16.000000000,5,2013625,P1M DUPLEX CYL.
if that is the case then you can use the below query

SELECT oe_line.customer_part_number AS 'Customer Part Number [1]', 
	invoice_hdr.po_no AS 'PO Number [2]', 
	sum(oe_line.qty_allocated) AS 'Qty Allocated [3]', 
	oe_line.line_no AS 'PO Line Number [4]', 
	invoice_line.item_id AS "Item ID / Serial # [5]", 
	invoice_line.item_desc AS "Description [6]"
	FROM [P21].[dbo].[oe_line]
	RIGHT OUTER JOIN [P21].[dbo].[invoice_line]
	ON oe_line.order_no = invoice_line.order_no
	RIGHT OUTER JOIN [P21].[dbo].[invoice_hdr]
	ON oe_line.order_no = invoice_hdr.order_no
	WHERE oe_line.order_no = '1003227'
group by oe_line.customer_part_number,invoice_hdr.po_no,oe_line.line_no,invoice_line.item_id,invoice_line.item_desc 

Open in new window

it would be nice if you can also post your expected results than just explaining it through code.
I'm working on that right now, it takes a while. I've got to parse 12 different queries and string together all the results. Stand by.
Actual data, expected results:
customer_part_number, po_no, qty_allocated, line_no, item_id, item_desc

157828-1,LLSS07454,0.000000000,1,DC-750-35,Quick Coupler Check Valve Assys.
2008153,LLSS07454,2.000000000,2,2008153,7 Station B5 Air Valve Assembly
B511BDB48C,LLSS07454,12.000000000,3,NULL,NULL
171902-1,LLSS07454,2.000000000,4,NULL,NULL
253443-1,LLSS07454,14.000000000,5,NULL,NULL
253444-1,LLSS07454,2.000000000,6,NULL,NULL
BM-38,LLSS07454,4.000000000,7,NULL,NULL
219P-4,LLSS07454,14.000000000,8,NULL,NULL
219P-6,LLSS07454,6.000000000,9,NULL,NULL
HARSCO AIR VALVE ASSYS.,LLSS07454,18.000000000,10,NULL,NULL
2013625,LLSS07454,0.000000000,11,2013625,P1M DUPLEX CYL
250975-1,LLSS07454,0.000000000,12,932638Q,31P5QHELEMASSYVITON

Open in new window

I hope this helps

;with cte as 
(
SELECT row_number() over (partition by oe_line.customer_part_number order by (select 1) ) as rn oe_line.customer_part_number AS 'Customer Part Number [1]', 
	invoice_hdr.po_no AS 'PO Number [2]', 
	oe_line.qty_allocated AS 'Qty Allocated [3]', 
	oe_line.line_no AS 'PO Line Number [4]', 
	invoice_line.item_id AS "Item ID / Serial # [5]", 
	invoice_line.item_desc AS "Description [6]"
	
	FROM [P21].[dbo].[oe_line]
	RIGHT OUTER JOIN [P21].[dbo].[invoice_line]
	ON oe_line.order_no = invoice_line.order_no
	RIGHT OUTER JOIN [P21].[dbo].[invoice_hdr]
	ON oe_line.order_no = invoice_hdr.order_no
	WHERE oe_line.order_no = '1003227'
)
select * from cte where rn = 1
)

Open in new window

I'm getting

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'oe_line'.
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near ')'.
Sorry, missed a comma in the above statement, corrected below
;with cte as 
(
SELECT row_number() over (partition by oe_line.customer_part_number order by (select 1) ) as rn, oe_line.customer_part_number AS 'Customer Part Number [1]', 
	invoice_hdr.po_no AS 'PO Number [2]', 
	oe_line.qty_allocated AS 'Qty Allocated [3]', 
	oe_line.line_no AS 'PO Line Number [4]', 
	invoice_line.item_id AS "Item ID / Serial # [5]", 
	invoice_line.item_desc AS "Description [6]"
	
	FROM [P21].[dbo].[oe_line]
	RIGHT OUTER JOIN [P21].[dbo].[invoice_line]
	ON oe_line.order_no = invoice_line.order_no
	RIGHT OUTER JOIN [P21].[dbo].[invoice_hdr]
	ON oe_line.order_no = invoice_hdr.order_no
	WHERE oe_line.order_no = '1003227'
)
select * from cte where rn = 1

Open in new window

It's almost perfect, except for some reason the Item ID and Description just repeat over and over...

rn,Customer Part Number [1],PO Number [2],Qty Allocated [3],PO Line Number [4],Item ID / Serial # [5],Description [6]
1,157828-1,LLSS07454,0.000000000,1,DC-750-35,Quick Coupler Check Valve Assys.
1,171902-1,LLSS07454,2.000000000,4,932638Q,31P5QHELEMASSYVITON
1,2008153,LLSS07454,2.000000000,2,DC-750-35,Quick Coupler Check Valve Assys.
1,2013625,LLSS07454,0.000000000,11,932638Q,31P5QHELEMASSYVITON
1,219P-4,LLSS07454,14.000000000,8,DC-750-35,Quick Coupler Check Valve Assys.
1,219P-6,LLSS07454,6.000000000,9,932638Q,31P5QHELEMASSYVITON
1,250975-1,LLSS07454,0.000000000,12,DC-750-35,Quick Coupler Check Valve Assys.
1,253443-1,LLSS07454,14.000000000,5,932638Q,31P5QHELEMASSYVITON
1,253444-1,LLSS07454,2.000000000,6,DC-750-35,Quick Coupler Check Valve Assys.
1,B511BDB48C,LLSS07454,12.000000000,3,932638Q,31P5QHELEMASSYVITON
1,BM-38,LLSS07454,4.000000000,7,DC-750-35,Quick Coupler Check Valve Assys.
1,HARSCO AIR VALVE ASSYS.,LLSS07454,18.000000000,10,932638Q,31P5QHELEMASSYVITON

Open in new window

I didn't understand what you meant, can you please elaborate, I cannot see that happening.
under revision, sorry, will post a new comment later
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial