Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    21

    Unanswered: Double Left Join?

    Helllo....


    I need to do a query that left joins a table conditionally.

    select t.*, v.vwap, a.moniker,v.dateTime
    from accounts a, stocks s, trades t left join vwaps v on t.[date] = v.dateTime
    where a.accountNumber = t.accountNumber AND t.symbol = s.symbol
    AND a.moniker not like '%kb001%'
    AND t.timeVWAP is not NULL
    ORDER BY a.moniker, t.date, t.[transaction], t.symbol


    but only left join vwaps if s.stockid = v.stockid

    I used to have it like this.

    select t.*, v.vwap, a.moniker,v.dateTime
    from accounts a, trades t, stocks s left join vwaps v on s.stockID = v.stockID
    where a.accountNumber = t.accountNumber AND t.symbol = s.symbol
    AND a.moniker not like '%kb001%'
    AND t.timeVWAP is not NULL
    ORDER BY a.moniker, t.date, t.[transaction], t.symbol

    in which case I would need to only join if v.dateTime = t.[date]
    else just attach a NULL value.
    Any idea how do to this?

  2. #2
    Join Date
    Nov 2002
    Location
    vienna
    Posts
    9

    Re: Double Left Join?

    try this

    select t.*, v.vwap, a.moniker,v.dateTime
    from
    accounts a,
    (
    stocks s
    join
    trades t
    on t.symbol = s.symbol
    )
    left join
    vwaps v on t.date = v.dateTime and s.stockid = v.stockid
    where a.accountNumber = t.accountNumber
    AND a.moniker not like '%kb001%'
    AND t.timeVWAP is not NULL
    ORDER BY a.moniker, t.date, t.transaction, t.symbol

  3. #3
    Join Date
    Sep 2003
    Posts
    21
    Will give it a try. Thanks.

Posting Permissions

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