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 '12 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 '12 at 11:20 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 45 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 '12 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1936
x265

asked: May 09 '12 at 10:40 AM

Seen: 648 times

Last Updated: May 09 '12 at 12:58 PM