Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    32

    Question Unanswered: Joining Table problem

    i have problem for get result from joining two table. better i show

    tbl.name
    name
    a
    b
    c

    tbl.cat
    name | reg_cat
    c 101
    a 100
    b 100
    c 100

    result

    i make it search condition where name in tbl.cat doesn't have reg_cat = 101
    then the result show

    sql = "SELECT tbl.name.*,tbl.cat.* " &_
    "FROM tbl.cat LEFT JOIN tbl.name ON tbl.name.name =tbl.cat.name WHERE tbl.cat. reg_cat <> '101'

    name |
    a
    b
    c

    how can i get result just like this

    name |
    a
    b

    cause c is already have that record. any idea ?

    tq

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    left join in the other direction

    condition in the ON, not WHERE

    then check for null, which indicates an unmatched row
    Code:
    select tbl.name.*
         , tbl.cat.* 
      from tbl.name 
    left outer
      join tbl.cat 
        on tbl.name.name 
         = tbl.cat.name 
       and tbl.cat.reg_cat = '101'
     where tbl.cat.name is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2003
    Posts
    32
    thanks r937 its works!

Posting Permissions

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