Re: SQL Technique Question - Mailing list pgsql-sql

> 
> On Jun 16, 2006, at 5:59 ,
> <operationsengineer1@yahoo.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