Re: SQL Query - Help with Joins
- From: Jonathan Ball <jonball@xxxxxxxxxxxxxx>
- Date: Thu, 28 Jun 2007 07:08:21 GMT
travo wrote:
Ahh, nifty - Thanks Jonathan - that makes it quite clear.
Is the efficiency of the query affected either way by the technique?
I don't know for certain without running it through the query analyzer, but I don't think so. A lot is going to depend on what indexes (or LFs) exist over the tables.
I should have stated in my first post that the first alternative I presented uses what's called a nested table expression. A variant of that would be to use something similar called a common table expression. Using the common table expression, it precedes the SELECT clause in the full select:
with bdgt as
(select * from budget
where month = june
and year = 2007)
select [column list]
from sales join bdgt on [etc.]
.
On Jun 26, 3:58 pm, Jonathan Ball <jonb...@xxxxxxxxxxxxxx> wrote:travo wrote:Ah, I nailed it. Rather than;An alternative is:
LEFT OUTER JOIN
budget ON
budget.sales guy = sales.sales guy
AND
budget.product group = SUBSTRING(sales.product group,1,3)
WHERE
1 = 1
AND budget.month = june
AND budget.year = 2007
I needed to include those clauses in the join;
LEFT OUTER JOIN
budget ON
budget.sales guy = sales.sales guy
AND budget.product group = SUBSTRING(sales.product group,1,3)
AND budget.month = june
AND budget.year = 2007
My nulls have appeared. Sweet!
LEFT JOIN
(select *
from budget
where month = june
and year = 2007) bdgt /* <-- note table
alias */
ON sales.sales_guy = bdgt.sales_guy
AND left(sales.product_group,3) = bdgt.product_group
The reason your original query didn't work is the
predicates in the WHERE clause of the full query are
applied to the *results* of the join. Another
alternative, one I don't like as much, would be like this:
LEFT JOIN budget
ON sales.sales_guy = budget.sales_guy
AND left(sales.product_group,3) = budget.product_group
WHERE (budget.month = june or budget.month is null)
AND (budget.year = 2007 or budget.year is null)
Chuck, thanks!
Travis
On Jun 26, 2:19 pm, travo <t...@xxxxxxxxxxxxxxx> wrote:
Hi Chuck,
Thanks for your reply. The query you have written is pretty much what
I have written, though I'm not getting the kinds of outcome we both
agree I should be getting.
Now for the details I guess. I'm not quite telling the whole truth
about the product group I'm joining on -
the sales table has a seven character product code of which the first
three characters set a parent product group (for which the budgets are
set) and the other four detail the sub groups and I'm doing a sneaky
SUBSTRING() to set the join.
My join looks like this;
LEFT OUTER JOIN
budget ON
budget.sales guy = sales.sales guy
AND
budget.product group = SUBSTRING(sales.product group,1,3)
Should this impact the results of the query?
As far as I can see, this is the only weird difference.
I am very grateful for your reply.
Regards,
Travis
On Jun 26, 1:33 pm, CRPence <crpe...@xxxxxxxxxxxx> wrote:
Other than amount not appearing in the select list, I would expect
the given query to produce the desired report. The following scripted
scenario seems to effectively match the given scenario, and the results
produced match the "intended outcome":
create table qtemp.budget
(sg char(2) ,pg int ,budget numeric(7, 0))
;
insert into qtemp.budget values
('06', 312, 400000)
,('06', 402, 300000)
;
create table qtemp.sales
(inv int ,sg char(2) ,pg int ,prod char(8)
,qty int ,amt numeric(6, 2) )
;
insert into qtemp.sales values
(555, '06', 312, 'widget ', 4, 400.00)
,(556, '06', 312, 'widget ', 3, 300.00)
,(557, '06', 402, 'sprocket', 7, 350.00)
,(558, '06', 510, 'cog ', 1, 100.00)
;
select s.sg ,s.pg ,s.prod ,s.qty ,s.amt ,b.budget
from qtemp.sales s
left outer join qtemp.budget b
on s.sg = b.sg
and s.pg = b.pg
-- report from the above SQL select:
SG PG PROD QTY AMT BUDGET
06 312 widget 4 400.00 400,000
06 312 widget 3 300.00 400,000
06 402 sprocket 7 350.00 300,000
06 510 cog 1 100.00 -
******** End of data ********
Note: The above scenario was performed on v5r4m0 using very current
fix levels
Regards, Chuck
-- All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer
travo wrote:
Hi, I'm having a little trouble trying to join a couple of tables --
one contains target budget data along side sales guys and product
groups, the other is the sales. For example;
-- budget
sales guy, product group, budget
06, 312, 400,000
06, 402, 300,000
-- sales
invoice, sales guy, product group, product, quantity, amount
555, 06, 312, widget, 4, 400.00
556, 06, 312, widget, 3, 300.00
557, 06, 402, sprocket, 7, 350.00
558, 06, 510, cog, 1, 100.00
-- intended outcome
sales guy, product group, product, quantity, budget
06, 312, widget, 4, 400.00, 400,000
06, 312, widget, 3, 300.00, 400,000
06, 402, sprocket, 7, 350.00, 300,000
06, 510, cog, 1, 100.00, (0 or null)
As you can see, the sales guy has sold stuff that he wasn't budgeted
to, so in the sales analysis, I still want to see his sale, but a zero
or null for the budget value.
My current query looks like this;
select
sales guy, product group, product, quantity, budget
from
sales
left outer join
budget on
budget.sales guy = sales.sales guy
and budget.product group = sales.product group
inner joins, right outer joins, left outer joins and fingers crossed
doesn't get me the result I'm after - I'm only seeing the three
budgeted sales, not the sale that wasn't budgeted for.
If you read this far, have you any suggestions? - Much appreciated.
Travis Winters
- References:
- SQL Query - Help with Joins
- From: travo
- Re: SQL Query - Help with Joins
- From: CRPence
- Re: SQL Query - Help with Joins
- From: travo
- Re: SQL Query - Help with Joins
- From: travo
- Re: SQL Query - Help with Joins
- From: Jonathan Ball
- Re: SQL Query - Help with Joins
- From: travo
- SQL Query - Help with Joins
- Prev by Date: Re: Line description check
- Next by Date: Re: SQL Query - Help with Joins
- Previous by thread: Re: SQL Query - Help with Joins
- Next by thread: Re: SQL Query - Help with Joins
- Index(es):
Relevant Pages
|
