bigger SQL statements are confusing

Results 1 to 2 of 2

Thread: bigger SQL statements are confusing

  1. #1
    Join Date
    Dec 1969

    Default bigger SQL statements are confusing

    Below is an SQL statement (sql server 2000) that I have found in previous code that I am using somewhat as a stepping stool to figure out how to design a more complicated SQL statement. I get really confused when you start grabbing all these variables from different tables. This seems like a really bad query (besides the fact it used the alphabet for naming) because it uses multiple definitions for the same column. Is this normal practice? It started making me really wonder. Also, Is there a good website to help build up my complicated SQL skills?<BR><BR><BR><BR>entity<BR>---------<BR>entity_id<BR>entity_name<BR>parent_entity<BR>< BR>route<BR>------------<BR>route_id<BR>supplier<BR>manufacturer<BR><BR>ro ute_step<BR>--------------<BR>route_step_id<BR>route_id<BR>origin<BR>destina tion<BR><BR>user_manufacturer_xref<BR>----------------<BR>user_id<BR>entity_id<BR><BR>select distinct d.entity_id, d.entity_name, f.parent_entity <BR> from route a, route_step b,<BR> user_manufacturer_xref c, entity d, route_step e, <BR> entity f, fogbreak_user g <BR> where (b.origin = g.entity_id or <BR> f.parent_entity = g.entity_id) <BR> and a.route_id = b.route_id and<BR> c.entity_id = a.manufacturer and <BR> c.user_id = ? and a.route_id = e.route_id <BR> and e.origin = d.entity_id <BR> and b.origin = f.entity_id <BR> and g.user_id = c.user_id <BR> order by d.entity_name

  2. #2
    Join Date
    Dec 1969

    Default Only thing I see wrong...

    ...are the idiot aliases used for the tables.<BR><BR>Aliases are fine, but they should *at least* be mnemonic.<BR><BR>You *SHOULD* use the same names for foreign keys that you use for primary keys, in my opinion. (Among other things, if you do that then *many* db tools will automatically assume the PK-FK relationship between the tables, and draw very pretty pictures with no help from you.)<BR><BR>Anyway:<BR><BR>SELECT distinct d.entity_id, d.entity_name, f.parent_entity <BR>FROM route AS RTE, route_step AS STEP1,<BR> user_manufacturer_xref AS XREF, entity AS ETY1, <BR> route_step AS STEP2, entity AS ETY2, fogbreak_user AS USR<BR>...<BR><BR>Or something along those lines, perhaps.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts