Joining Table problem

zakyk · #1 (**permalink**) 05-14-04, 05:12

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

r937 · #2 (**permalink**) 05-14-04, 05:43

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

zakyk · #3 (**permalink**) 05-16-04, 20:56

thanks r937 its works!

Internet Services

Web Development

Digital Marketing

Consumer Tech