"well one of us is right ... or both wrong .. or something else" I think that pretty much covers all the bases. :-) can we go home now?
On Wed, Oct 1, 2008 at 3:21 PM, rob wolfe <[EMAIL PROTECTED]> wrote: > > 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 -~----------~----~----~----~------~----~------~--~---
