Help With Query

Question

my query is returning inaccurate result sets, because I only expect to have two rows returned, but I get 8. I should have ONE row for LineNumber = 3 and ONE row for LineNumber = 5 - this is my DDL

Create Table orderInfo
(
    id int
    ,companyname varchar(250)
    ,orderdate datetime2
    ,ordernumber varchar(100)
    ,ponumber varchar(250)
)

Insert into orderinfo Values
(15703, 'Homephotons', '2021-03-25 15:13:38.5900000', 'HP-10061', NULL)

Create Table lineInfo
(
    id int
    ,orderid int
    ,linenumber int
    ,quantity int
    ,unitprice decimal(16,4)
    ,deliverymethod varchar(250)
    ,accountmanagercode varchar(100)
    ,rc decimal(16,4)
)

Insert Into lineInfo Values
(108543, 15703, 1, 1, '0.00', 'FedEx', 'AC203', '12.23')
,(108544, 15703, 2, 19, '2.95', 'FedEx', 'AC203', '0.00')
,(108545, 15703, 3, 150, '2.22', 'FedEx', 'AC203', '0.00')
,(108546, 15703, 4, 500, '0.44', 'FedEx', 'AC203', '0.00')
,(108547, 15703, 5, 500, '0.78', 'FedEx', 'AC203', '0.00')

Create Table linePart 
(
    lineid int
    ,vendorname varchar(500)    
    ,sku varchar(500)
    ,name varchar(500)
)

Insert Into linePart Values
(108543, NULL, 'SKU_1', 'Name_1')
,(108544, NULL, 'SKU_2', 'Name_2')
,(108545, 'Valid', 'SKU_3', 'Name_3')
,(108546, NULL, 'SKU_4', 'Name_4')
,(108547, 'Valid', 'SKU_5', 'Name_5')


Create Table shipInfo
(
    id int
    ,ordersId int
    ,shipdate datetime2
)

Insert Into shipInfo Values
(17308, 15703, '2021-06-29 13:17:20.3230000')
,(17383, 15703, '2021-04-23 16:29:43.9630000')
,(17432, 15703, '2021-04-20 09:20:21.5100000')
,(17458, 15703, '2021-04-15 12:55:13.5070000')


Create Table shipLines
(
    shipmentsId int
    ,quantityShipped int
    ,orderlineid varchar(100)
)

Insert Into shipLines Values
(17308,150, 'HP-10061-3')
,(17383,1,'HP-10061-1')
,(17383,500,'HP-10061-5')
,(17432,500,'HP-10061-4')
,(17458,19, 'HP-10061-2')

And this is query

Select * from (
    Select
    oi.CompanyName
    ,OrderDate = CAST(oi.OrderDate As Date)
    ,ShipDate = CAST(si.ShipDate As Date)
    ,oi.OrderNumber
    ,oi.PoNumber
    ,li.LineNumber
    ,(select top 1 VendorName from linepart lp where lp.LineId=li.id) VendorName
    ,(select top 1 Name from linepart lp where lp.LineId=li.id) Name
    ,(select top 1 Sku from linepart lp where lp.LineId=li.id) Sku
    ,li.Quantity
    ,(select top 1 QuantityShipped from shipLines sl where sl.ShipmentsId=si.id) QuantityShipped
    ,li.UnitPrice
    ,li.DeliveryMethod
    ,li.accountmanagercode
    ,li.rc
    from [orderInfo] oi
    inner join lineinfo li on li.orderid = oi.id
    join shipInfo si on si.OrdersId = oi.id
    ) selected
where selected.VendorName ='Valid'
Order By selected.OrderNumber, selected.LineNumber ASC

Answer 1

Select * from (
     Select
     oi.CompanyName
     ,OrderDate = CAST(oi.OrderDate As Date)
     ,ShipDate = CAST(d.ShipDate As Date)
     ,oi.OrderNumber
     ,oi.PoNumber
     ,li.LineNumber
     ,(select top 1 VendorName from linepart lp where lp.LineId=li.id) VendorName
     ,(select top 1 Name from linepart lp where lp.LineId=li.id) Name
     ,(select top 1 Sku from linepart lp where lp.LineId=li.id) Sku
     ,li.Quantity
     ,(select top 1 QuantityShipped from shipLines sl where sl.ShipmentsId=d.id) QuantityShipped
     ,li.UnitPrice
     ,li.DeliveryMethod
     ,li.accountmanagercode
     ,li.rc
     from [orderInfo] oi
     inner join lineinfo li on li.orderid = oi.id
 cross apply (select top 1 id, CAST(si.ShipDate As Date) ShipDate from shipInfo si 
where si.ordersId=oi.id order by si.ShipDate  /*DESC */) d

) selected
 where selected.VendorName ='Valid'
 Order By selected.OrderNumber, selected.LineNumber ASC

Answer 2

You only have OrderId (or OrdersId) columns to connect LineInfo to the Order and ShipInfo to the order. So if the order has, say, 5 lines and 3 rows in the ShipInfo for the same order, your result will be 15 rows. Is there any connection between LineInfo and ShipInfo? If not, your result is correct or you may want to select the earliest ShipDate per order instead to get one row per LineInfo. 2 LineItems with correct vendor x 4 ShipInfo rows = 8. If you exclude ShipInfo from your select you will get correct number of rows - 2.

Answer 3

Hi，@Johnathan Simpson

Welcome to Microsoft T-SQL Q&A Forum!

I agree with naomi , I think your subquery is too cumbersome , you just want to filter out the valid data by linking multiple tables , can you explain the meaning of shipdate for your data?
Let us know what you think.

 select * from orderInfo oi  
inner join  lineinfo li  
on li.orderid=oi.id  
inner join shipInfo si on si.ordersId=oi.id  
inner join linePart lp on lp.lineid=li.id  
 where vendorname='Valid'  
  Order By OrderNumber, LineNumber   

Best regards,
Bert Zhou

Answer 4

Hi @Johnathan Simpson

I think you should create a field in the TABLE [shipLines] to correspond to the linenumber field in the TABLE [lineInfo] ，currently the two tables cannot have a one-to-one relationship，so you can’t have just two rows returned.

Best regards,
Isabella