Grab first value from joined table NOT create row for each value in joined table

[barnettjacob]

Guys,

I've got a very simple Query that is part of a pick-list creator for customer orders. One of the fields I need to bring through is Warehouse location. This complicated matters as at present if there is more than one location for a product it effectively creates a double up of the order line!

In the code below, pw.location is my warehouse location - all I want to do is bring through the first available location.

Any help most appreciated.
Jacob

select

convert(varchar(10),h.CreationDate,103) as 'Creation Date',
convert(varchar(10),h.insertionDate,103) as 'Insertion Date',
h.CustomerOrderNumber,
H.CustomerCode,
(case when H.DeliveryName is null then c.firstname + ' ' + c.Surname else h.DeliveryName end) as 'Name',
H.DeliveryAddress1,
H.DeliveryAddress2,
H.DeliveryAddress3,
H.DeliveryAddress4,
H.PostCode,
c.email,
d.productcode,
d.Quantity,
pw.locationcode

from eee..customerorderheader H
left join eee..customerorderdetail D on D.CustomerOrderNumber = H.CustomerOrderNumber
left join eee..customer C on C.customercode = H.customercode
left join eee..Product P on P.productcode = d.ProductCode
left join eee..productwarehouselocation pw on pw.productcode = d.productcode and pw.WarehouseBranchCode = '94'

where h.branchcode = '100' and h.CreationDate >='09/11/2012' and h.StatusT = 'Settled' and p.NonInventory = 0

 

[r937]

there is no row sequence in relational databases, unless you create and maintain a user-defined column to store a sequence value, e.g. the latest date the warehouse was added to the table (requires a date_added column)

alternatively, you could use the lowest or highest warehouse number, if that makes sense

but in terms of "the first one that the query finds" there's no way to do that

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[barnettjacob]

Rudi, thanks for the super quick response.

Although I have an 'insert' date in the warehouse location table it would appear to be exactly the same for many of the productcodes that have multiple locations meaning that I probably can't use it as a 'tie breaker'.

 

[philhege]

I had a similar puzzle today, in which I had to remove duplicate entries for a value.

The table has two fields: widget number, and widget count.

The table has more than one entry for each widget number. My task was to create a result with unique widget numbers (they didn't care about the second field).

So, a bit of Googling, et voila:

WITH cte AS (    SELECT *,          
ROW_NUMBER() OVER (PARTITION BY WidgetNumber ORDER BY WidgetCount DESC) AS rn    
FROM dbo.MyWidgetTable ) 
SELECT WidgetNumber, WidgetCount INTO dbo.MyNewWidgetTable FROM cte WHERE rn = 1

With a bit more work, you can adapt this approach, and maybe even return a preferred warehouse location.

That said, your GUI should show locations and balances in a sub-table, to give an overall picture of part availability. But that's outside the scope of SQL, of course ;^)

-----------
With business clients like mine, you'd be better off herding cats.