how to return with a nested query two columns from the same table but different results

Jonathan Brotto 420

I have a query where it is a parent query returning results then other results from another table but that table would have two results I would like such as a bill to and ship to where I would like one in each column in the parent query but in the child table are two line items. how would I go about that?

I am thinking of a nested query within the select statement. it has been awhile. any approach works for me.

Jonathan Brotto 420 Reputation points

2024-08-23T13:33:50.0366667+00:00

@Bruce (SqlWork.com) The goal is not sure more nested or subquery but to use another table that would have the bill to and ship to information and return it to transaction query.

2 answers

Bruce (SqlWork.com) 65,211 Reputation points

2024-08-22T16:17:07.2366667+00:00

without a schema and sample data all we can say is use a join (left if optional) or sub query.
Please sign in to rate this answer.
Jonathan Brotto 420 Reputation points

2024-08-22T17:14:42.8033333+00:00

I wrote this query and it returns double the lines as I referred to a table twice as I want to get ship to and bill to info on a single line in the query result.

LEFT JOIN inbound850.tb_InboundOrdersReferences b on H.ID = B.HeaderID

LEFT JOIN inbound850.tb_InboundOrdersReferences S on H.ID = S.HeaderID

SELECT h.OrderNumber, h.OrderDate, h.ProcessDate, h.ProcessDate, h.ShipTo, h.branch, h.PartnerID, b.RefType, b.RefValue, i.VeritivProductCode as 'Compass Code', i.CustomerProductCode, i.Description, i.CustDesc, i.Quantity, i.OriginalQuantity, i.UM, i.CustUM, i.PricePer, i.UnitPrice,i.LineComments AS 'Customer UM', i.CustomerLineNumber, i.UPC FROM inbound850.tb_InboundOrdersHeaders H INNER JOIN inbound850.InboundOrdersItems I ON H.ID = I.HeaderID LEFT JOIN inbound850.tb_InboundOrdersReferences b on H.ID = B.HeaderID LEFT JOIN inbound850.tb_InboundOrdersReferences S on H.ID = S.HeaderID WHERE h.PartnerID LIKE '%Lob%' And h.OrderDate >= '2024-07-01' AND b.RefType = 'BT_N104'

Erland Sommarskog 111.2K Reputation points MVP

2024-08-22T21:34:15.84+00:00

In the query above, you are not using the S alias, so why not remove it?

Also, the condition b.RefType = 'BT_N104' should be moved to ON. If you have it in the WHERE clause, the left join effectively becomes an inner join.

LiHongMSFT-4306 27,016 Reputation points

2024-08-23T03:36:45.81+00:00

Why left join the same table twice with same on condition.

LEFT JOIN inbound850.tb_InboundOrdersReferences b on H.ID = B.HeaderID LEFT JOIN inbound850.tb_InboundOrdersReferences S on H.ID = S.HeaderID

Jonathan Brotto 420 Reputation points

2024-08-23T13:31:40.71+00:00

@Erland Sommarskog and others I was going to include as part of the condition in where clause

AND s.RefType = 'ST_N104'. Then return values based on that next to corresponding as it is fetching bill to and ship to information.

Erland Sommarskog 111.2K Reputation points MVP

2024-08-23T20:56:38.22+00:00

Well, you would add the condition to the JOIN itself:

LEFT JOIN inbound850.tb_InboundOrdersReferences b on H.ID = B.HeaderID AND b.RefType = 'BT_N104' LEFT JOIN inbound850.tb_InboundOrdersReferences S on H.ID = S.HeaderID AND s.RefType = 'ST_N104'

If you put the conditions in the WHERE clause, you transform the outer join to an inner join.

So you are saying that both these joins can give multiple matches. That is not good. You will first need to decide how to deal with that on a factual level. That is, what result do you get?

Sure, the query provided by Naomi will remove the duplicates, but it will also just arbitrarily pick one row.

Jonathan Brotto 420 Reputation points

2024-08-23T21:21:32.3533333+00:00

I wrote this query and it returns double the lines as I referred to a table twice as I want to get ship to and bill to info on a single line in the query result.

LEFT JOIN inbound850.tb_InboundOrdersReferences b on H.ID = B.HeaderID

LEFT JOIN inbound850.tb_InboundOrdersReferences S on H.ID = S.HeaderID

SELECT h.OrderNumber, h.OrderDate, h.ProcessDate, h.ProcessDate, h.ShipTo, h.branch, h.PartnerID, b.RefType, b.RefValue, i.VeritivProductCode as 'Compass Code', i.CustomerProductCode, i.Description, i.CustDesc, i.Quantity, i.OriginalQuantity, i.UM, i.CustUM, i.PricePer, i.UnitPrice,i.LineComments AS 'Customer UM', i.CustomerLineNumber, i.UPC FROM inbound850.tb_InboundOrdersHeaders H INNER JOIN inbound850.InboundOrdersItems I ON H.ID = I.HeaderID LEFT JOIN inbound850.tb_InboundOrdersReferences b on H.ID = B.HeaderID LEFT JOIN inbound850.tb_InboundOrdersReferences S on H.ID = S.HeaderID WHERE h.PartnerID LIKE '%Lob%' And h.OrderDate >= '2024-07-01' AND b.RefType = 'BT_N104'

Jonathan Brotto 420 Reputation points

2024-08-23T21:23:01.5066667+00:00

@Erland Sommarskog For any time I would like to have a condition in the join it will filter out the results before the join?

Erland Sommarskog 111.2K Reputation points MVP

2024-08-23T21:38:06.89+00:00

Look at this to see the difference:

CREATE TABLE #a (a int NOT NULL, b int NOT NULL) INSERT #a(a, b) VALUES(1, 1), (2, 2), (3, 3) CREATE TABLE #b (b int NOT NULL, c int NOT NULL) INSERT #b(b, c) VALUES(2, 2), (3, 3), (4, 4) SELECT * FROM #a a LEFT JOIN #b b ON a.b = b.b WHERE b.c = 3 SELECT * FROM #a a LEFT JOIN #b b ON a.b = b.b AND b.c = 3 go DROP TABLE #a, #b

As of your other post, it is just repeats what you said yesterday, so it is not bringing the discussion forward.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Naomi Nosonovsky 7,856

Try using OUTER APPLY for both current left joins and include reftype condition as part of the WHERE in these OUTER APPLY statements, e.g.

SELECT h.OrderNumber, h.OrderDate, h.ProcessDate, h.ProcessDate, 
h.ShipTo, h.branch, h.PartnerID, 
b.RefType, b.RefValue as BillTo, -- guessing here
s.RefType, s.RefValue as ShipTo, -- guessing here

i.VeritivProductCode as 'Compass Code', 
i.CustomerProductCode, i.Description, 
i.CustDesc, i.Quantity, i.OriginalQuantity, i.UM, i.CustUM, i.PricePer, 

i.UnitPrice,i.LineComments AS 'Customer UM', i.CustomerLineNumber, i.UPC 
FROM inbound850.tb_InboundOrdersHeaders H 
INNER JOIN inbound850.InboundOrdersItems I ON H.ID = I.HeaderID 


OUTER APPLY (SELECT TOP (1) * from inbound850.tb_InboundOrdersReferences b WHERE H.ID = B.HeaderID 
and 
b.RefType = 'BT_N104') b
OUTER APPLY (SELECT TOP (1) * FROM inbound850.tb_InboundOrdersReferences S WHERE H.ID = S.HeaderID 
AND S.RefType = 'ST_N104') S

WHERE h.PartnerID LIKE '%Lob%' And h.OrderDate >= '2024-07-01' ;

Share via

how to return with a nested query two columns from the same table but different results

2 answers

Your answer