Results 1 to 2 of 2

Thread: Subquery Issue

  1. #1
    Join Date
    Jul 2011
    Posts
    6

    Unanswered: Subquery Issue

    Hello, Iím trying to get some help with the code below. Iím basically trying to pull all of the transportation data for a particular customer #. For instance, how many pallets does the customer have on a truck, what stores did the truck stop at, etc. The problem however is that multiple (different) customers might ride on the same truck and if thatís the case Iím looking to pull that data as well. Essentially, Iím currently running the query with a specific customer # specified, finding all of the route #ís that a particular customer rode on, removing the customer # filter and then re-running the query using the route #ís in order to get a comprehensive set of data that includes all of the data for any truck that this particular customer rides on. Also, Iím using an older version of sequel which has been giving me some issues as I try to fix it as well. I know it probably seems confusing and Iím not exactly a SQL expert but any help would be appreciated. Thanks in advance.


    select
    [Week_Ending],
    [Cust_#], Chain,

    Round(Sum(Distance),0) Distance, Sum(Weight) Weight, Sum(Volume) Volume, Sum(Pieces) Pieces, Sum(Pallets) Pallets,

    Facility,
    Ledgend, [Descriptions for Ledgend],
    [Route #], [Route Group],
    Dispatch, [Original Stop #],
    City, State, Zip


    From
    TBL_Data


    Where
    Week_Ending = 'we010111'

    --Substring(Week_Ending,7,2) = '11'

    AND Facility IN ('Georgia')

    --AND (Chain Like '%Store%')

    AND (Ledgend Like 'DELIVERED')

    And [Route #] In ('22-111','33-111')

    --And [Cust_#] in ('11111','22222')


    Group By
    [Week_Ending],
    [Cust_#], Chain,
    Facility,
    Ledgend, [Descriptions for Ledgend],
    [Route #], [Route Group],
    Dispatch, [Original Stop #],
    City, State, Zip


    Order By
    Dispatch desc, [Route #] desc, [Original Stop #] asc
    ;

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You would want to use something called a correlated sub query for this. Try googling 'sql exists'. You will use this in your where clause
    Code:
     where exists (select ' pootle' from truck_journeys where truck_Id = truck_id and customer_id = @customer_id)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •