February 8, 2006 at 10:24 am
Hi
I am trying to join to three tables however one is duplicated.
DPSITE TABLE:
DPSITE
UK
FRANCE
SPAIN
DPHEADER TABLE
DPSITE DPCRFF DPMJFF DPMNFF DPTTGA DPTTFP DPISDJ
UK 5 4 4 5 5 106111
UK 5 8 4 5 6 106010
UK 5 3 3 5 5 106011
FRANCE 2 5 6 3 3 106001
FRANCE 4 7 6 3 3 106012
FRANCE 2 5 7 3 8 106011
F5574A TABLE
QGDTEJ CALDTE
106009 2006-01-09
106010 2006-01-10
106011 2006-01-11
106012 2006-01-12
106013 2006-01-13
106014 2006-01-14
All I want out of this for the DPSITE table to display all its records ie each country even if there is NOT a record in the DPHEADER table
when I run the SQL below it does not display the county spain
SELECT D.DPSITE, SUM(A.DPCRFF), SUM(A.DPMJFF),
SUM(A.DPMNFF), SUM(A.DPTTGA), SUM(A.DPTTFP),
SUM(A.DPTTGI) FROM WSTSTDATA/DPSITE D LEFT OUTER JOIN
WSTSTDATA/DPHEADER A ON D.DPSITE = A.DPSITE
JOIN WSTSTDATA/F5574A B ON A.DPISDJ = B.QGDTEJ
JOIN WSTSTDATA/F5574A C ON A.DPISDJ = C.QGDTEJ
AND B.CALDTE >= '2006-01-09' AND C.CALDTE <= '2006-01-14'
GROUP BY D.DPSITE ORDER BY 6 DESC
but I cannot run
SELECT D.DPSITE, SUM(A.DPCRFF), SUM(A.DPMJFF),
SUM(A.DPMNFF), SUM(A.DPTTGA), SUM(A.DPTTFP),
SUM(A.DPTTGI) FROM WSTSTDATA/DPSITE D LEFT OUTER JOIN
WSTSTDATA/DPHEADER A ON D.DPSITE = A.DPSITE
Left outer JOIN WSTSTDATA/F5574A B ON A.DPISDJ = B.QGDTEJ
Left outer JOIN WSTSTDATA/F5574A C ON A.DPISDJ = C.QGDTEJ
AND B.CALDTE >= '2006-01-09' AND C.CALDTE <= '2006-01-14'
GROUP BY D.DPSITE ORDER BY 6 DESC
because it will display spain but does not filter on the dates?
can anyone help
if you need more info please ask
thanks in advance Paul
February 8, 2006 at 11:58 am
I would put intermediate results in a temp table and them filter by another query.
Regards,Yelena Varsha
February 9, 2006 at 1:39 am
I think I have solved it what do you all think?
SELECT D.DPSITE, SUM(A.DPCRFF), SUM(A.DPMJFF),
SUM(A.DPMNFF), SUM(A.DPTTGA), SUM(A.DPTTFP),
SUM(A.DPTTGI) FROM WSTSTDATA/DPSITE D LEFT OUTER JOIN
WSTSTDATA/DPHEADER A ON D.DPSITE = A.DPSITE LEFT OUTER
JOIN WSTSTDATA/F5574A B ON A.DPISDJ = B.QGDTEJ
WHERE
B.CALDTE >= '2006-01-09' AND B.CALDTE <= '2006-02-08'
OR A.DPSITE IS NULL
GROUP BY D.DPSITE ORDER BY 6 DESC
February 9, 2006 at 4:16 am
Well, it does return a result, but are the numbers the ones you expect?
I can't tell unless you explain more about how the DPHEADER and F5574A tables are related to each other, and how you want to treat rows in DPHEADER that have no match on dpisdj = qgdtej even though the date falls within range. Should such be inlcuded in the sums or not?
Here's another way of writing a query that produces the same result:
select d.dpsite, sum(a.dpcrff), sum(a.dpmjff), sum(a.dpmnff), sum(a.dpttga), sum(a.dpttfp), sum(a.dpisdj) as dpisdj
from #dpsite d
left join
( #dpheader a
join #f5574a b
on a.dpisdj = b.qgdtej
and b.caldte >= '2006-01-09' and b.caldte <= '2006-02-08'
)
on d.dpsite = a.dpsite
group by d.dpsite
order by dpisdj desc
PS. Never order by position, always use names in order by clauses.
/Kenneth
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply