I agree with Mike on the idea of looking at lots and lots of code. Good and bad. I sometimes learn more from code that I know is crap than the code that I know is good.
As for thought process, (shit, Mike said that too ... anyway) I think in terms of sets. This means that I tend to start out with "ok, this is the universe of all records" and then start tossing them away. As far as joins are concerned, I often draw pictures, not necessarily (ok, never) perfect little ER diagrams but something to help me wrap my head around the relationships. I am a very visual person, probably why i ended up doing what I do for a living. Rob On Mon, September 28, 2009 13:10, Michael Moore wrote: > I don't understand this: > > "TABLE_A has only 1 record for each logical_name, TABLE_B can have > multiple." > > Table_b can have multiple records for each logical_name? Is there some > kind > of relationship between "logical_name" and "relationship_name"? > > As for thought process, I tend to think in terms of similarities. For > example, "this SQL problem is like another one that I encountered". So, > there are patterns, or templates. If your goal is to improve your ability > to > write creative SQL solutions, then I would suggest looking at many > examples > of working SQL. The more SQL queries you examine and work through, the > more > you will see patterns of use.. > > Regards, > Mike > > On Sun, Sep 27, 2009 at 7:12 PM, JJ <[email protected]> wrote: > >> >> I wanted to ask about what methodology do you SQL gurus use to build >> queries? How do you go about in your thinking process? >> >> I know how to build simple queries but I often get stuck on what the >> proper approach is on the more complex ones. >> >> For example I have this situation where I need to update a table based >> on conditions that are partially from that table and partially from a >> related table. >> >> Would you split something like this into subqueries and start from the >> bottom-most subquery or something like that? >> >> >> A simplified example of these tables are: >> >> TABLE_A FIELDS: >> logical_name >> type >> lifecycle_status >> >> >> TABLE_B FIELDS: >> relationship_name >> type >> active >> >> >> TABLE_A has only 1 record for each logical_name, TABLE_B can have >> multiple. >> >> I need to set the "active" field in TABLE_B to "false" for records >> that meet the following criteria: >> >> TABLE_A.LOGICAL_NAME = 'DEVICE_1' >> OR TABLE_A.LOGICAL_NAME = 'DEVICE_2' >> OR TABLE_A.LOGICAL_NAME = 'DEVICE_3' >> >> AND TABLE_A.TYPE = 'database' >> AND CIRELATIONSHIPM1.BC_TYPE = 'SERVICE-DATABASE' >> AND CIRELATIONSHIPM1.BC_ACTIVE = 't' >> >> AND TABLE_A.BC_LIFECYCLE_STATUS = 'RETIRED' >> OR COUNT( CIRELATIONSHIPM1.RELATIONSHIP_NAME ) > 1) >> >> >> >> Thanks in advance, >> >> > >> > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] 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 -~----------~----~----~----~------~----~------~--~---
