[thelist] SQL Query help

[rudy]

> I'm trying to make it as flexible as possible.

are you taking suggestions?

use JOIN syntax

join conditions typically never change -- if they do, you are changing
primary key structures and foreign key references, and who ever does that,
eh?

separate the join conditions from the where conditions, which typically
do change, often all the time

if you view the query below in monospace, you will see a gutter separating
syntax elements (select, from, where, group by, order by) from application
elements (column and table names, expressions, etc.)

i write my connectors on the left of the gutter, because visually they are
easier to see over there, and therefore to inspect for syntax errors, than
connectors at the end of the application elements, for which your eye has to
travel to the end of each variable-length line in order to notice if a comma
is missing or a superfluous comma exists

dunno why i would focus on that particular point but you can guess

sorry to use your query as the example

;o)


anyhow, here's how i would write your query --

      select a.merchantName   AS merchantName
           , b.downloadCost   AS cost
           , b.merchBank
           , b.merchType
           , c.filePath       AS filePath
        from users_t a
      inner
        join merchants_acct b
          on a.merchantName = b.merchantName
      inner
        join filepaths_t c
          on b.merchantName = c.merchantName
       where a.user_id =  'user'
         and a.user_password =  'password'
         and b.merchActive =  '1'


note the type of join (inner, left outer, cross, etc.) is on a line by
itself -- i like how that stands out, as you can recognize instantly what
sort of results to expect from the query

notice also how you can now quickly verify that each table is correctly
connected to the others by the appropriate join conditions

debugging many misbehaving queries has taught me that form follows function

if the function is understanding sql, form that conveys semantics is
preferred

use JOIN syntax

you will thank yourself when debugging your own queries one day


rudy