• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Sql query isssue

 
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why is the result-set different for both the following queries; I thought they should be the same ?



select * from p123 left outer join (p117 inner join p039 on lglstat117=lglcode039) on fnbr123=fnbr117 and refid123=refid117
and enddate117=0 where cnty123='55' and div123='4' and enddate123=0;


select * from p123 left outer join (p117 inner join p039 on lglstat117=lglcode039) on fnbr123=fnbr117 and refid123=refid117
where cnty123='55' and div123='4' and enddate123=0 and (enddate117=0 or enddate117 is null)



Your comments - very much appreciated; enddate117 is Integer type if that is of any extra help
Thanks
 
Marshal
Posts: 79177
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In the first instance (at least I think so, I am not certain) you are doing the join on enddate117 = 0, and in the second instance you are selecting but not joining on that enddate.
 
Greenhorn
Posts: 24
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

prasad agarwal wrote:

select * from p123 left outer join (p117 inner join p039 on lglstat117=lglcode039) on fnbr123=fnbr117 and refid123=refid117
and enddate117=0 where cnty123='55' and div123='4' and enddate123=0;


select * from p123 left outer join (p117 inner join p039 on lglstat117=lglcode039) on fnbr123=fnbr117 and refid123=refid117
where cnty123='55' and div123='4' and enddate123=0 and (enddate117=0 or enddate117 is null)



Give names to the columns such that it can be easily understandable prasad !!!
reply
    Bookmark Topic Watch Topic
  • New Topic