Home Forums SQL Server 2005 T-SQL (SS2K5) How do you use a sub query in a join? RE: How do you use a sub query in a join?

  • August 10, 2012 at 11:33 am

    #1524573

    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!