0

I use the following sql command to search some records.

select con.type, con.contract_id, con.account, chk.account, chk.check_flags
from contract_scan_image con, outer check_customer_info chk
where con.type='AP' and con.account=chk.account

and got the result like the following.

type  contract_id  account      account      check_flags                    

AP    1413178      03071800181                                             
AP    1413774      03071800569                                             
AP    1414218      03071800810                                             
AP    1415937      03071900602  03071900602  000000222010000000000000000000
AP    1417948      03072000524  03072000524  000000222010000000000000000000
AP    1417999      03072000555                                             
AP    1418203      03072000667  03072000667  000000222010000000000000000000
AP    1418216      03072000672  03072000672  000000220010000000000000000000
......

Why the records are still found even if there are nothing in the field "account" from the table ?

Johnny
  • 633
  • 3
  • 9
  • 21
  • 1
    Because that's exactly what an `OUTER` join does; keeps the rows even if there wasn't a match. If you want only results where there is a match, use `INNER JOIN` *(Also, I strongly suggest not using the `,` notation for joins as it was replace by the `ANSI92` standard, as in 1992, over 20 years ago... Search the web for `INNER JOIN`)* – MatBailie Aug 29 '13 at 06:31
  • Got it. It's all my concept wrong... However, I found the sytax of inner join in informix should be like that: select con.type, con.contract_id, con.account, chk.account, chk.check_flags from contract_scan_image con, check_customer_info chk where con.type='AP' and con.account=chk.account – Johnny Sep 02 '13 at 01:39
  • Are you sure INFORMIX doesn't support INNER JOIN? That's the syntax introduced since 1992... – MatBailie Sep 02 '13 at 16:15
  • Actually, I am not sure since I have worked on this for just few months... What I did is just testing it by typing and running. And it showed "201: A syntax error has occured." But it works fine if "INNER JOIN" is replaced by "," . Maybe the version I am using is too old to support "INNER JOIN"? How can I check it? – Johnny Sep 03 '13 at 02:03

1 Answers1

1

cause you are using outer join which will bring the row in the first table even if there is no match with the other table try writign the statment like this

select con.type, con.contract_id, con.account, chk.account, chk.check_flags
from contract_scan_image con inner join check_customer_info chk on con .account = chk.account
where con.type='AP'
wala rawashdeh
  • 423
  • 5
  • 17
  • Yes, it's all my concept wrong... However, see my comment above for the sytax of informix for inner join. Thank you. – Johnny Sep 02 '13 at 01:44