Re: SQL Technique Question

>
> On Jun 16, 2006, at 5:59 ,
> <operationsengineer1(at)yahoo(dot)com> wrote:
>
> > i frequently join certain tables together in
> various
> > tables.
> >
> > view the following link for an example:
> >
> > http://www.rafb.net/paste/results/mBvzn950.html
> >
> > is it a good practice to leave this included in
> the
> > queries, as is, or should i factor it out somehow?
> if
> > i should factor it, how do i do so?
>
> I'm not quite sure what you're getting at in your
> message (in
> particular, what is the "this" in "leave this
> included in the
> queries"?), and it looks like I have a completely
> different
> interpretation of what you're asking, looking at the
> responses you've
> already received from Andrew and Rod. I think you're
> asking about
> encapsulation and how to efficiently use code,
> rather than copying
> and pasting the basic query and then modifying a
> small portion of it.
> Based on that interpretation, here's what I'd do.
> (If I'm wrong,
> well, then, oh well.)
>
> I'd create a view that contains the common code.
>
> CREATE VIEW t_inspect_join_view AS
>
> SELECT t_inspect_result.inspect_result_pass
> , t_inspect_result.inspect_result_timestamp
> , t_product.product_number
> , t_inspect_result.inspect_result_id
> FROM t_inspect_result, t_inspect, t_inspect_area,
> t_serial_number,
> t_link_contract_number_job_number,
> t_job_number, t_product
> WHERE t_inspect_result.inspect_id =
> t_inspect.inspect_id
> AND t_inspect.serial_number_id =
> t_serial_number.serial_number_id
> AND
> t_serial_number.link_contract_number_job_number_id =
>
>
t_link_contract_number_job_number.link_contract_number_job_number_id
> AND
> t_link_contract_number_job_number.job_number_id =
> t_job_number.job_number_id
> AND t_inspect.inspect_area_id =
> t_inspect_area.inspect_area_id
> AND t_product.product_id =
> t_job_number.product_id;
>
> or in a style I find a bit clearer:
>
> CREATE VIEW t_inspect_join_view AS
>
> SELECT t_inspect_result.inspect_result_pass
> , t_inspect_result.inspect_result_timestamp
> , t_product.product_number
> , t_inspect_result.inspect_result_id
> FROM t_inspect_result
> JOIN t_inspect USING (inspect_id)
> JOIN t_serial_number USING (serial_number_id)
> JOIN t_link_contract_number_job_number
> USING (link_contract_number_job_number_id)
> JOIN t_inspect_area USING (inspect_area_id)
> JOIN t_job_number USING (job_number_id)
> JOIN t_product USING (product_id);
>
> One of the reasons I like this style is that it
> makes it easy to see
> that all of the tables in the FROM clause have join
> conditions (which
> is usually what you want). For example, it looks
> like you probably
> want the
>
> AND t_inspect.inspect_area_id =
> t_inspect_area.inspect_area_id
>
> part of the WHERE clause in your "repeating code"
> section, so I've
> added it to the view. With the JOIN conditions (how
> tables are joined
> together) now part of the FROM clause, the WHERE
> clause can be used
> to list just restrictions restrictions (limiting
> what rows are
> returned). I find this much clearer to write and
> read, as I've got
> clear separation between these two things. While
> underneath it all
> the server might consider everything part of the
> WHERE clause,
> sytactically I find this style helpful.
>
> I've also added some more columns to the SELECT
> target list, as
> you'll want to have them exposed for the extra WHERE
> clause
> restrictions. Once part of the view, only columns
> listed in the
> SELECT target list will be accessible outside of the
> view. You may
> have other restrictions that you want to apply in
> different cases, so
> you may want to add more columns to the target list.
>
> So your original query, using this view, would look
> like:
>
> SELECT inspect_result_pass
> FROM t_inspect_join_view
> WHERE product_number = '7214118000'
> AND inspect_result_timestamp > '2006-01-01'
> AND inspect_result_timestamp < '2006-06-13'
> AND inspect_result_id IN ...
>
> Hope this helps.
>
> Michael Glaesemann
> grzm seespotcode net

Michael, this is the answer to my question. i have to
read up on the other answers to see if i can learn
something to incorporate into my programming.

i've just had a bear of a time trying to keep
everything straight... okay, i want to know what
inspects are associated with p/n 123, s/n 1... or
what is the p/n associated with p/n 456?

i had to troll through all my relations to get at
results.

i need to become mor familiar with views. if i use
views, will i substantially impair performance?

thanks for the answer... the end result sure sure
looks clean.

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com