Join returning multiple rows but need blank

I am writing a simple query that shows sales orders and corresponding purchase orders joined on the item number.

The problem I have is the sales order Item number could have multiple Purchase orders for that item number so i have done a LEFT OUTER JOIN on the item number and that works but the sales order lines are being duplicated and it is confusing the data.

So what i get now is.

Order|Line|Item|PO No|

What i would like is.
| | |PO002
| | |PO002

Thanks in advance.

more ▼

asked May 09, 2012 at 10:40 AM in Default

avatar image

0 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

What is being asked for in terms of a data query is returning the right answer. What you want to 'see' is a presentation issue.

To blank out the Order, Line and Item columns, you need to get your presentation layer (application code, report writer, whatever it is you are using to present the data to the user) to handle it.

Not saying you couldn't come up with a T-SQL query to do it, just saying it's the wrong place to do it.

more ▼

answered May 09, 2012 at 11:20 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

Agree with suggest and not sure why a Left Join is needed to return the data, an Inner Join would still return the multiple rows. Might be worth OP reading up on http://msdn.microsoft.com/en-us/library/aa213233(v=sql.80).aspx

May 09, 2012 at 12:58 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 09, 2012 at 10:40 AM

Seen: 910 times

Last Updated: May 09, 2012 at 12:58 PM

Copyright 2018 Redgate Software. Privacy Policy