#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Location
    São Paulo - Brazil
    Posts
    35
    Rep Power
    2

    Left join with where


    I would like to left join all rows of the table a that have a column named STATIVO null or different of 'I' with a table b.

    I wrote:

    use UNIS022_SGBR_UNNO_AI
    select a.IDBEM,
    a.cdodi as cdodi_fis,
    cdtpinstalacao as cdtpinstalacao_fis,
    cdtpundcad as cdtpundcad_fis
    ,cdtpbem as cdtpbem_fis
    ,cditem2 as cditem2_fis
    ,cditem3 as cditem3_fis
    ,cditem4 as cditem4_fis
    ,cditem5 as cditem5_fis
    ,cditem6 as cditem6_fis
    ,cdiduc as cdiduc_fis
    ,cduar as cduar_fis
    ,cdcm as cdcm_fis
    ,cdmaterial as cdmaterial_fis
    from ttelecom200 a
    left join vtpt902_fis b
    on a.idbem = b.idbem
    and where (a.STATIVO is null or a.STATIVO <> 'I')

    Is this right? If no, which is the correct form?

    Should I must use an alternative format without left join? Which?

    Greetings from São Paulo - Brazil

    Ricardo
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    Originally Posted by nightrider43
    Is this right?
    no -- remove the word AND in front of the word WHERE
    Code:
    SELECT ...
      FROM ttelecom200 AS a
    LEFT OUTER
      JOIN vtpt902_fis AS b
        ON b.idbem = a.idbem 
     WHERE a.stativo IS NULL 
        OR a.stativo <> 'I'

    Comments on this post

    • nightrider43 agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Location
    São Paulo - Brazil
    Posts
    35
    Rep Power
    2
    The word "and" you mentioned was typing error.

    Thank you for your help

    Cheers

    Ricardo

IMN logo majestic logo threadwatch logo seochat tools logo