Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How do you use a sub query in a join? Expand / Collapse
Author
Message
Posted Friday, August 10, 2012 11:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 336, Visits: 596
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 > ''
Post #1343556
Posted Friday, August 10, 2012 11:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 12,921, Visits: 32,285
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1343560
Posted Friday, August 10, 2012 11:51 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 3,908, Visits: 8,860
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1343575
Posted Tuesday, August 14, 2012 7:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 336, Visits: 596
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.
Post #1344690
Posted Tuesday, August 14, 2012 7:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 336, Visits: 596
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.

0085177217 900003 0002619226 0002619226 55
0085177217 900003 0002619226 0002619226 55
0085177217 900003 0002619226 0002619226 55
0085177217 900003 0002619226 0002619226 55
0085177217 900003 0002619226 0002619226 55
0085177217 900003 0002619226 0002619226 55
0085177217 900003 0002619226 0002619226 55
0085177217 900003 0002619226 0002619226 55
Post #1344696
Posted Tuesday, August 14, 2012 7:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 6,861, Visits: 14,160
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1344699
Posted Tuesday, August 14, 2012 7:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 336, Visits: 596
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.
Post #1344701
Posted Tuesday, August 14, 2012 7:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 3,908, Visits: 8,860
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1344710
Posted Tuesday, August 14, 2012 7:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 6,861, Visits: 14,160
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1344713
Posted Wednesday, August 15, 2012 7:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 336, Visits: 596
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
Post #1345250
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse