I everybody i need help to done a simple query that doesn't work. So i
have a table where there are days of sale for many stores. Sometime in
year some of those stores are closed cause of holyday. But in my query
i want a row to show me 0 sales. I tried outers joins on a time table
contaning all dates of my month, but none of these 3 joins (right,
left ,full) works. I'm working on this since 1 day and i'm now in a
dead end. Does someone got this prob before or have a idea to solve
this prob.

Guillaume wrote:
show us what you have so far

"Guillaume" <guillaumelortie (AT) gmail (DOT) com> wrote:

zac.carey (AT) gmail (DOT) com wrote:


Temp orders Table of date

_________ _______________

Date Loc Qty DATE
_______________
30 july Mtl 30 Full 30 july
30 july Mtl 30
02 august Mtl 26 outer 01 august I wish
01august Mtl NULL
03 august Mtl 22 Join 02 august
02 august Mtl 26
30 july NY 33 on 03 august
03 august Mtl 22
01 august NY 85 Date
30 july NY 33
02 august NY 35
01 august NY 85
03 august NY 21
02 august NY 35

03 august NY 21

but i never get the new row : 01 august Mtl NULL
here there is my query :
select *
FROM dbo.Temp_rptComparatif
full outer join
(
select [Date]
from .dbo.Times
where date between (select Min(Date) from
dbo.Temp_rptComparatif )
and (select Max(Date) from dbo.Temp_rptComparatif )
)
ON (dbo.Temp_rptComparatif.[Date] = Times.[Date] )

thank you

Guillaume wrote:
How does the words Full Outer Join on Date get into the result set???

And you never will as long as there is no such row in the temp_orders
table.
Actually what you are missing is this row:
date location Qty DATE
NULL NULL NULL 01 august

So are you sure the results you showed us are what comes out of that
query??
And I must have missed it, but what DBMS are you using? What version?
on what platform (OS)?

Ed

Yes i know that my indications was'n to clear. I finally did it with a
cross join joinning a left join. First time i tried

tmp_table left join [all my dates] but it never works . and a cross
join instead of left was coorect only if i do that

select *
(select * from locations cross join [all my dates] ) left join (select
* from temp_table )

so thank guys to interesting my probleme event if my massage aren't
always clear

Your sample data is a little difficult to interpret by folding lines.
This is my understanding. If it is different, please point it out.

CREATE TABLE Temp_orders
(oDate DATE NOT NULL
,Loc CHAR(3) NOT NULL
,Qty INTEGER
);


INSERT INTO Temp_orders
VALUES
('2006-07-30', 'Mtl', 30)
,('2006-08-02', 'Mtl', 26)
,('2006-08-03', 'Mtl', 22)
,('2006-07-30', 'NY', 33)
,('2006-08-01', 'NY', 85)
,('2006-08-02', 'NY', 35)
,('2006-08-03', 'NY', 21);


CREATE TABLE Table_of_date
(tDate DATE NOT NULL);

INSERT INTO Table_of_date
VALUES
('2006-07-30')
,('2006-08-01')
,('2006-08-02')
,('2006-08-03');

If the data is OK, you may get your required result by this query.
(DBMS is DB2 UDB on Windows)
SELECT tDate AS Date
, COALESCE(tc.Loc, to.Loc) AS Loc
, Qty
FROM Table_of_date td
JOIN
(SELECT DISTINCT Loc
FROM Temp_orders
) tc
ON 0=0
LEFT OUTER JOIN
Temp_orders to
ON to.oDate = td.tDate
AND to.Loc = tc.Loc
ORDER BY
Date, Loc
;
----------------------------------------------------------

DATE LOC QTY
---------- --- -----------
2006-07-30 Mtl 30
2006-07-30 NY 33
2006-08-01 Mtl -
2006-08-01 NY 85
2006-08-02 Mtl 26
2006-08-02 NY 35
2006-08-03 Mtl 22
2006-08-03 NY 21

8 record(s) selected.