Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2013
    Posts
    6

    Unanswered: right / left join with multiple where clauses

    hi im new in here...i want to ask about this :

    WITH Table1(ID , product) AS (
    VALUES
    (a , 'Mouse')
    , (b , 'Keyboard')
    , (c , 'Speaker' )
    , (d , 'Remote' )
    )
    , Table2(stockid , month, year , id) AS (
    VALUES
    (4 , 1 , 2013 , a )
    , (5 , 1 , 2013 , b )
    , (6 , 2 , 2013 ,c )
    , (7 , 2 , 2013 ,d)
    , (8 , 1 , 2013 ,a )
    )

    i want to have some result like this
    id product month year stokID
    ----------- ----- ----------- ---------
    a Mouse 1 2013 4
    b Keyboard 1 2013 5
    c Speaker NULL NULL NULL
    d Remote NULL NULL NULL


    my query in my sql
    select t1.id,t1.product,t2.moth,t2.year,t2.stokid
    from t1
    left join t2
    on t2.id = t1.id
    where t2.month = 1 and t2.year = 2013



    and the result just

    id product month year stokID
    ----------- ----- ----------- ---------
    a Mouse 1 2013 4
    b Keyboard 1 2013 5



    can u help me resolve this???

    thx for your help

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    because
    you join on on t2.id = t1.id and add left rows
    finally you filter on
    where t2.month = 1 and t2.year = 2013
    as these rows do not qualify, you don't see them
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Aug 2013
    Posts
    6
    Quote Originally Posted by przytula_guy View Post
    because
    you join on on t2.id = t1.id and add left rows
    finally you filter on
    where t2.month = 1 and t2.year = 2013
    as these rows do not qualify, you don't see them
    so where syntax not working in that case??so cant u help me for the best syntax ?? thx

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Code:
    WITH T1(ID , product) AS (
    VALUES
      ('a' , 'Mouse')
    , ('b' , 'Keyboard')
    , ('c' , 'Speaker' )
    , ('d' , 'Remote' )
    )
    , T2(stockid , month, year , id) AS (
    VALUES
      (4 , 1 , 2013 , 'a')
    , (5 , 1 , 2013 , 'b')
    , (6 , 2 , 2013 , 'c')
    , (7 , 2 , 2013 , 'd')
    , (8 , 1 , 2013 , 'a')
    )
    select t1.id,t1.product,t2.month,t2.year,t2.stockid
    from t1
    left join t2
    on t2.id = t1.id and t2.month = 1 and t2.year = 2013
    Regards,
    Mark.

  5. #5
    Join Date
    Aug 2013
    Posts
    6
    Quote Originally Posted by mark.b View Post
    Hi,

    Code:
    WITH T1(ID , product) AS (
    VALUES
      ('a' , 'Mouse')
    , ('b' , 'Keyboard')
    , ('c' , 'Speaker' )
    , ('d' , 'Remote' )
    )
    , T2(stockid , month, year , id) AS (
    VALUES
      (4 , 1 , 2013 , 'a')
    , (5 , 1 , 2013 , 'b')
    , (6 , 2 , 2013 , 'c')
    , (7 , 2 , 2013 , 'd')
    , (8 , 1 , 2013 , 'a')
    )
    select t1.id,t1.product,t2.month,t2.year,t2.stockid
    from t1
    left join t2
    on t2.id = t1.id and t2.month = 1 and t2.year = 2013
    thx mark for your help, i've never know now we can add multiple condition for join,, i thought i can filter with 'where' syntax

Posting Permissions

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