How do you use a sub query in a join?

Question

How do you use a sub query in a join?

dwilliscp

SSCrazy Eights

Points: 9270
More actions
August 10, 2012 at 11:27 am

#262553

Looking to take the following query:
SELECT sales_order_number
, count(sales_order_number) as Notification_cnt
from ztb_IMP_Notifications
group by sales_order_number
and use it to feed the ztb_IMP_Notifications section of the below query:
SELECT ztb_Carrier_Delivery_Metrics.Delivery
, ztb_Carrier_Delivery_Metrics.Delivery_Item
, ztb_IMP_Notifications.Sales_Order_Number
, ztb_Carrier_Delivery_Metrics.REF_DOC
-- insert Notification_cnt here
FROM ztb_Carrier_Delivery_Metrics INNER JOIN
ztb_IMP_Notifications ON ztb_Carrier_Delivery_Metrics.REF_DOC = ztb_IMP_Notifications.Sales_Order_Number
where ztb_carrier_Delivery_Metrics.Ref_Doc > ''

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

it's going to look somethign like this: i wasn't sure ont eh join criteria: i was assuming the table ztb_IMP_Notifications has the same sales_order_number

SELECT

ztb_Carrier_Delivery_Metrics.Delivery,

ztb_Carrier_Delivery_Metrics.Delivery_Item,

ztb_IMP_Notifications.Sales_Order_Number,

ztb_Carrier_Delivery_Metrics.REF_DOC,

-- insert Notification_cnt here

MyAlias.Notification_cnt

FROM ztb_Carrier_Delivery_Metrics

INNER JOIN ztb_IMP_Notifications

ON ztb_Carrier_Delivery_Metrics.REF_DOC = ztb_IMP_Notifications.Sales_Order_Number

LEFT OUTER JOIN (SELECT

sales_order_number,

COUNT(sales_order_number) AS Notification_cnt

FROM ztb_IMP_Notifications

GROUP BY sales_order_number

) MyAlias

ON ztb_IMP_Notifications.sales_order_number = MyAlias.sales_order_number

WHERE ztb_carrier_Delivery_Metrics.Ref_Doc > ''

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

You could also consider using CTEs.

WITH Notifications AS (

SELECT sales_order_number

, count(sales_order_number) as Notification_cnt

FROM ztb_IMP_Notifications

GROUP BY sales_order_number)

SELECT ztb_Carrier_Delivery_Metrics.Delivery

, ztb_Carrier_Delivery_Metrics.Delivery_Item

, n.Sales_Order_Number

, ztb_Carrier_Delivery_Metrics.REF_DOC

, n.Notification_cnt

FROM ztb_Carrier_Delivery_Metrics cdm

INNER JOIN Notifications n ON cdm.REF_DOC = n.Sales_Order_Number

WHERE cdm.Ref_Doc > ''

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 3

Thanks for your help... I just could not figure out how to word the statement ... looking at some samples I found on the internet.

Thanks for the help from both of you.

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 4

Checking the data.. there might be something wrong with the join too... I would expect that all this would be on one line... but we might be getting extra lines for some other reason...MRP Area, maybe.

00851772179000030002619226000261922655

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

SELECT

d.Delivery,

d.Delivery_Item,

n.Sales_Order_Number,

d.REF_DOC,

n.Notification_cnt

FROM ztb_Carrier_Delivery_Metrics d

INNER JOIN (

SELECT

sales_order_number,

Notification_cnt = COUNT(sales_order_number)

FROM ztb_IMP_Notifications

GROUP BY sales_order_number

) n

ON n.Sales_Order_Number = d.REF_DOC

WHERE d.Ref_Doc > ''

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 6

dwilliscp (8/14/2012)
Thanks for your help... I just could not figure out how to word the statement ... looking at some samples I found on the internet.
Thanks for the help from both of you.

yep, problem with the join.. I need to know that there were 55 Notifications, but not get 55 rows. I guess we could do a distinct on the outside query.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

Is REF_DOC a unique value? Or do you have it several times in ztb_Carrier_Delivery_Metrics?

As I see it, this is a problem with your data.

EDIT: Could you show us your code and sample data to be sure?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

dwilliscp (8/14/2012)
dwilliscp (8/14/2012)
Thanks for your help... I just could not figure out how to word the statement ... looking at some samples I found on the internet.
Thanks for the help from both of you.
yep, problem with the join.. I need to know that there were 55 Notifications, but not get 55 rows. I guess we could do a distinct on the outside query.

How many rows does this return?

SELECT

d.Delivery,

d.Delivery_Item,

--n.Sales_Order_Number,

d.REF_DOC--,

--n.Notification_cnt

FROM ztb_Carrier_Delivery_Metrics d

WHERE d.REF_DOC = '0002619226'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 9

ChrisM@Work (8/14/2012)
dwilliscp (8/14/2012)
dwilliscp (8/14/2012)
Thanks for your help... I just could not figure out how to word the statement ... looking at some samples I found on the internet.
Thanks for the help from both of you.
yep, problem with the join.. I need to know that there were 55 Notifications, but not get 55 rows. I guess we could do a distinct on the outside query.
How many rows does this return?
SELECT
d.Delivery,
d.Delivery_Item,
--n.Sales_Order_Number,
d.REF_DOC--,
--n.Notification_cnt
FROM ztb_Carrier_Delivery_Metrics d
WHERE d.REF_DOC = '0002619226'

You get 6 rows, different deliveries though. (since it seems to omit tabs, I am going to try spaces. The Prime Key in the Carrier Delivery Metrics is Delivery, Line..but the Notifications file only has sales order. Ok does not like spaces so will Use underscores)

Delivery__________Line_________Sales Order

0085180733______900015_______0002619226

0085186304______900003_______0002619226

0085159667______900003_______0002619226

0085177217______900003_______0002619226

0085184918______900012_______0002619226

0085192416______900001_______0002619226

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 10

Luis Cazares (8/14/2012)
Is REF_DOC a unique value? Or do you have it several times in ztb_Carrier_Delivery_Metrics?
As I see it, this is a problem with your data.
EDIT: Could you show us your code and sample data to be sure?

The problem with the data is there is no good way to link the two. The only data that exists in both is the Sales Order (Ref_Doc in Metrics and Sales_Order_Number in Notifications). The idea is to total up the number of records, in Notification, that match the Sales Order ... then use a query to merge that data with the Metrics... and store for quick reporting. Since Notifications does not contain the Sales Order Line,... and even this would not stop all over counting since we ship more than once on a sales order line..., there is going to be over counting.

The notification file is the issues whe had with a sales order. There is a filter that I am currently working on that will filter out all issues but the shipment related ones. Interesting, our company uses different codes for different divisions.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 11

Have you used one of the solutions we gave you?

Lowell's might return duplicate rows (because of the extra inner join), but Chris' and mine should not have that problem.

Could you post the code you used? that way we won't be shooting in the dark.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Looks like you need to roll up ztb_Carrier_Delivery_Metrics too. Try this:

SELECT

d.MAX_Delivery,

d.MAX_Delivery_Item,

n.Sales_Order_Number,

d.REF_DOC,

n.Notification_cnt

FROM (

SELECT

REF_DOC,

MAX_Delivery = MAX(Delivery),

MAX_Delivery_Item = MAX(Delivery_Item)

FROM ztb_Carrier_Delivery_Metrics

GROUP BY REF_DOC) d

INNER JOIN (

SELECT

sales_order_number,

Notification_cnt = COUNT(sales_order_number)

FROM ztb_IMP_Notifications

GROUP BY sales_order_number

) n

ON n.Sales_Order_Number = d.REF_DOC

WHERE d.Ref_Doc > ''

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 13

Luis Cazares (8/15/2012)
Have you used one of the solutions we gave you?
Lowell's might return duplicate rows (because of the extra inner join), but Chris' and mine should not have that problem.
Could you post the code you used? that way we won't be shooting in the dark.

I was using Lowell's, but you end up with over counting...due to the linking of sales order header, it would be best if we could link on Delivery+Line...but the data does not support this. :

SELECT

ztb_Carrier_Delivery_Metrics.Delivery,

ztb_Carrier_Delivery_Metrics.Delivery_Item,

ztb_IMP_Notifications.Sales_Order_Number,

ztb_Carrier_Delivery_Metrics.REF_DOC,

MyAlias.Notification_cnt

FROM ztb_Carrier_Delivery_Metrics

INNER JOIN ztb_IMP_Notifications

ON ztb_Carrier_Delivery_Metrics.REF_DOC = ztb_IMP_Notifications.Sales_Order_Number

LEFT OUTER JOIN (SELECT

sales_order_number,

COUNT(sales_order_number) AS Notification_cnt

FROM ztb_IMP_Notifications

GROUP BY sales_order_number

) MyAlias

ON ztb_IMP_Notifications.sales_order_number = MyAlias.sales_order_number

WHERE ztb_carrier_Delivery_Metrics.Ref_Doc > ''

I was able to get material added to the extract, so my code now has the following join

ztb_Carrier_Delivery_Metrics.material = ztb_IMP_Notifications.material_number

That cut the rows returned down from: 11642391 to 5591132. Looking at the difference between a left outer join and a simple select on Ztb_Carrier_Delivery_Metrics you get an extra 1,106 rows. I will try the code just posted and see what I get.

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 14

I ran the below statement.. with a modification for a link on material number (cutting the list down by 638 from the posted code). I will run this past the stakeholders, this will get rid of the over-counting but we are guessing that all notifications fall under the highest delivery+item. Still this might be our best shot.

SELECT

d.MAX_Delivery,

d.MAX_Delivery_Item,

n.Sales_Order_Number,

d.REF_DOC,

n.Notification_cnt

FROM (

SELECT

REF_DOC,

MAX_Delivery = MAX(Delivery),

MAX_Delivery_Item = MAX(Delivery_Item)

FROM ztb_Carrier_Delivery_Metrics

GROUP BY REF_DOC) d

INNER JOIN (

SELECT

sales_order_number,

Notification_cnt = COUNT(sales_order_number)

FROM ztb_IMP_Notifications

GROUP BY sales_order_number

) n

ON n.Sales_Order_Number = d.REF_DOC

WHERE d.Ref_Doc > ''