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