Help With Query

Question

question

InigoMontoya-1790 asked Apr 21, '22 NaomiNNN commented Apr 22, '22

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

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

pituach · Apr 22 at 08:46 AM

Well done on providing the DDL+DML

Most people forget it.

+1

0 ·

Answer 1 · 2022-04-22T14:04:00.117Z

JingyangLi answered Apr 22, '22 JingyangLi edited Apr 22, '22

 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

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

Answer 2 · 2022-04-22T02:31:47.06Z

NaomiNNN answered Apr 22, '22 NaomiNNN commented Apr 22, '22

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

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

InigoMontoya-1790 · Apr 22 at 01:32 PM

@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 ·

NaomiNNN InigoMontoya-1790 · Apr 22 at 01:49 PM

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 ·

InigoMontoya-1790 NaomiNNN · Apr 22 at 02:12 PM

@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 ·

Show more comments

Answer 3 · 2022-04-22T06:40:21.34Z

BertZhoumsft-7490 answered Apr 22, '22 InigoMontoya-1790 commented Apr 22, '22

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

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

InigoMontoya-1790 · Apr 22 at 01:25 PM

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 ·

InigoMontoya-1790 InigoMontoya-1790 · Apr 22 at 01:32 PM

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 ·

Answer 4 · 2022-04-22T07:26:14.857Z

IsabellaZhangMSFT-7746 answered Apr 22, '22 pituach commented Apr 22, '22

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.

Best regards,
Isabella

image.png (30.3 KiB)

· 3

5 |1600 characters needed characters left characters exceeded

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

pituach · Apr 22 at 08:44 AM

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 ·

InigoMontoya-1790 pituach · Apr 22 at 01:25 PM

Unfortunately unable to modify DDL of the tables.

0 ·

pituach InigoMontoya-1790 · Apr 22 at 01:53 PM

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 ·

question

Help With Query

4 Answers

question details

question

Help With Query

4 Answers

question details

Related Questions