Outer apply on another outer apply?

Question

I have a problem with an SQL request I am working on.

I would like to return the total number of a certain item that is ordered by a certain customer. The problem is: I get more than one line when I execute my request.

(Please note that this is a very simplified version of my request to help the comprehension of the code. My tables are not named like this, neither are my rows.)

SELECT outerpallet.pallet as palletnbr, ID, shipmentID FROM arandomtable
OUTER APPLY (SELECT SUM(palletnbr) as pallet FROM thepallettable GROUP BY arandomtable.ID) as outerpallet
WHERE ID = @something

What I get:

palletnbr  ID    shipmentid
1          2     340
2          3     340
3          4     340

What I would like to get (whitout necessarily having the ID and shipmentid):

palletnbr
6

To do this I would have to get all my lines in one. I thought of doing another outer apply, but it won't work (since I am doing an outer apply of another outer apply)...

I thought of maybe doing my outer apply in another way, but I can't seem to find out how. I tried to add:

GROUP BY shipmentid

But it would have to be in the OUTER APPLY, and I can't seem to figure out how to put it there.

Would anyone have an idea that could help me to solve this problem ? Any little hint would be of a great help !

score 0 · Answer 1 · answered Jul 15 '13 at 18:33

0

I realize that your query might be more complicated. The version in your question seems more easily answers with a join than with cross apply:

SELECT SUM(palletnbr) as palletnbr, arandomtable.ID, arandomtable.shipmentID
FROM arandomtable left outer join
     thepallettable 
     on thepallettable.randomID = arandomtable.ID
WHERE arandomtable.ID = @something
group by arandomtable.ID, arandomtable.shipmentID;

Is there a reason you need cross apply?

answered Jul 15 '13 at 18:33

Gordon Linoff

1,242,037
58
646
786

My querry would look more like somthing like this: SELECT (...) FROM arandomtable INNER JOIN x on y = z INNER JOIN x on y = z INNER JOIN x on y = z INNER JOIN x on y = z INNER JOIN x on y = z LEFT JOIN x ON y = z LEFT JOIN z on y = z OUTER APPLY (SELECT SUM(palletnbr) as pallet FROM thepallettable GROUP BY arandomtable.ID) as outerpallet WHERE (condition) – AquaSoley Jul 15 '13 at 18:49
So the outer apply would be the only way that I've seen to get what I want. – AquaSoley Jul 15 '13 at 18:50

score 0 · Accepted Answer · answered Jul 16 '13 at 19:33

Ok, so I have gotten to do what I wanted to do, following a little the answer of Gordon. Here is the code I went with, if it can ever be of interest to someone that would have the same question I had:

SELECT outerpallet.pallet as palletnbr, ID, shipmentID FROM arandomtable
--(here are all of my other join that do not affect the question)
OUTER APPLY (SELECT SUM(palletnbr) as pallet 
FROM thepallettable 
INNER JOIN shipmenttable ON shipmenttable.shipmentid = thepallettable.shipmentid
INNER JOIN shipmentdetail ON shipmenttable.shipmentid = shipmentable.shipmentdtlid
GROUP BY shipmentdetail.ID) as outerpallet
WHERE ID = @something

This is a very shortened and basic version, but it might help someone that is stuck. I basically put some other INNER JOIN on my OUTER APPLY, so it helped me get more precision. Surely this is not the most effective way to do so, but this was ok in my case since it was used in a small part of the project, and thus would not be solicited by more than one customer (this was a client personalisation of our main program).

Thank you very much !

Outer apply on another outer apply?

2 Answers2