x

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|
SO0001|1|A1|PO001
SO0001|1|A1|PO002
SO0001|1|A1|PO003
SO0001|2|A2|NULL

What i would like is.
SO0001|1|A1|PO001
| | |PO002
| | |PO002
SO0001|2|A2|NULL

Thanks in advance.

more ▼

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

davidhole gravatar image

davidhole
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

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1951
x293

asked: May 09, 2012 at 10:40 AM

Seen: 754 times

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