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