well one of us is right ... or both wrong .. or something else i wasnt sure if only needed the stuff from A or if there was something else from B was needed so i just (as per usual) decided to dump the pair of tables ... I am such a messy coder some days
Michael Moore wrote: > based on my understanding of the problem > > select * from membermaster a > where not exists (select 1 > from Memberdetl b > where a.mbrno = substr( b.mbrsep,1,8) > and mbrfee > 0); > Mike > > On Wed, Oct 1, 2008 at 2:43 PM, Rob Wolfe <[EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]>> wrote: > > > On Oct 1, 5:14 pm, j <[EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]>> wrote: > > I was hoping I could get some help with a query. I have a read of a > > membermaster file (table a) and it uses the key member to read > table > > b (memberdetl). The key is mbrno varchar 8 > > Memberdetl also has a field called mbrsep which is varchar 10 the > > first 8 characters are mbrno and the last 2 are the seperator. For > > instance: > > > > Membermaster-mbrno 12345678 = Memberdetl-mbrno 12345678. but > > memberdetl-mbrsep can = > > 123567801, 1234567802, 1234567803 etc. I want to read thru all the > > mbrseps and if any have a mbrfee > 0 I want to throw that record > > away and go read the next table A record until eof > > and this version of it makes more sense than the one I just > answered ... > > select a.*,b.* > from membermaster a > join Memberdetl b on a.mbrno = substr(b.mbrsep,1,8) > where nvl(b.mbrfee,0) = 0 > > the nvl is just in case you can have an empty fee field, you dont want > to leave them out .. or maybe you do .. i just assumed you didnt > > you might or might not want a distinct in there as well ... depends on > what your data looks like > > This is actually not that difficult a problem (meaning that it is the > kind of thing that you have to do all the time - and of course > assuming that I didnt misread it completely - which does happen) and > if you are thinking about EOF's you are REALLY on the wrong track. > With Oracle databases, or any relational databases for that matter you > should always think in terms of sets of data as a first step. Don't > make the mistake of treating a database as just a bunch of flat files > because it just isnt the case. > > > > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
