question

InigoMontoya-1790 avatar image
1 Vote"
InigoMontoya-1790 asked NaomiNNN commented

Help With Query

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Well done on providing the DDL+DML

Most people forget it.

+1

0 Votes 0 ·
JingyangLi avatar image
1 Vote"
JingyangLi answered JingyangLi edited
 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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered NaomiNNN commented

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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@NaomiNNN -> Our DBA modified the shipLines table to have a orderlineid column, could we join on everything to the right of the - to the linenumber to get an accurate result set returned?

0 Votes 0 ·
NaomiNNN avatar image NaomiNNN InigoMontoya-1790 ·

Hi,

I usually use a CTE technique to get results I want in a simpler way, so - idea only:

 ;;with OrderLines as (select O.*, OL.Id as LineID from Orders O inner join LineInfo OL on O.Id = OL.OrderID where exists (select 1 from LinePart LP where LP.LineID = OL.Id and Vendor = 'Valid')),
 shipmentInfo as (select Sh.*, SHL.* from ShipInfo Sh inner join ShipLines SHL ON Sh.Id = SHL.ShipmentsID)
    
 select OL.*, SI.* from OrderLines OL LEFT JOIN ShipmentInfo SI on OL.OrderID = SI.OrdersID and OL.LineID = SI.OrderLineID;

The above is just a sketch of the solution, you would modify according to your needs to return appropriate columns as needed and named correctly.

0 Votes 0 ·

@NaomiNNN - when i try to run the query I get this error Msg 207, Level 16, State 1, Line 3
Invalid column name 'OrderID'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'OrderLineID'.

0 Votes 0 ·
Show more comments
BertZhoumsft-7490 avatar image
0 Votes"
BertZhoumsft-7490 answered InigoMontoya-1790 commented

Hi,@InigoMontoya-1790

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




· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

using this query, I get same result as in my query....I get 4 rows for linenumber 3 and 4 rows for linenumber 5....i should only get ONE row for each of those.

It seems the issue is joining in the shipInfo table, if I remove that join I see what I expect just with no ship dates, but I MUST have ship dates in the query.

0 Votes 0 ·

Our DBA modified the shipLines table to have a orderlineid column, could we join on everything to the right of the - to the linenumber to get an accurate result set returned?

0 Votes 0 ·
IsabellaZhangMSFT-7746 avatar image
0 Votes"
IsabellaZhangMSFT-7746 answered pituach commented

Hi @InigoMontoya-1790

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.
195389-image.png

Best regards,
Isabella



image.png (30.3 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


OFF-Tpoic: I am wondering what and how CELKO was responding if he saw "create a field in the TABLE" :-)

Well... usually I would not comment on this if it was coming from the OP, but we should remember that Table does not have fields but rows and columns. We should try to use the right term (Fields are logical entities in Forms).

0 Votes 0 ·

Unfortunately unable to modify DDL of the tables.

0 Votes 0 ·
pituach avatar image pituach InigoMontoya-1790 ·

Hi

Can you please describe the expected result set according to your sample data and please explain how you get this result out of the source tables.

The request is not fully clear to me

0 Votes 0 ·