Full Outer Join with 2 tables doesn't work

[MeGGaN]

Hi, I have to make a report of all the articles that didn't have sales on a given period of time.

Each article is located on different sites:

InvtID Site QtyAvail
art1 1 311
art1 5 0
art1 6 0
art2 2 500
art3 1 450

The sales are grouped also by site like this:

InvtID Site Date
art1 1 2003/11/27
art1 5 2003/11/01
art2 2 2003/11/15

I don't care of the sales amount, only the date.

I need a result like this:

InvtID Site QtyAvail InvtID2 Site2 Date
art1 1 311 art1 1 27/11/2003
art1 5 0 art1 5 01/11/2003
art1 6 0 NULL NULL NULL
art2 2 500 art2 2 15/11/2003
art3 1 450 NULL NULL NULL

I need to filter on Crystal Reports and leave only records with null results (unless I can do it with the query...) like this:

InvtID Site QtyAvail InvtID2 Site2 Date
art1 6 0 NULL NULL NULL
art3 1 450 NULL NULL NULL

(this are the articles without sales)

The problem is that I am getting the oposite result:

InvtID Site QtyAvail InvtID2 Site2 Date
art1 1 311 art1 1 27/11/2003
art1 5 0 art1 5 01/11/2003
art2 2 500 art2 2 15/11/2003

This is the query I'm using:

SELECT i.InvtID, i.SiteID, i.QtyAvail,
s.InvtID as InvtID2, s.SiteID as SiteID2, s.InvcDate
FROM Inventory i FULL OUTER JOIN Sales s ON (i.SiteID=s.SiteID) AND (i.InvtID=s.InvtID)
WHERE s.InvcDate>= '2003-12-01 00:00:00' AND s.InvcDate< '2003-12-27 00:00:00'
ORDER BY i.ClassID, i.InvtID, i.SiteID

I'm not sure if I can link the 2 tables using the 2 key fields... I think that is why the full outer doesn't work but I don't know how to solve it :S
FROM Inventory i FULL OUTER JOIN Sales s ON (i.SiteID=s.SiteID) AND (i.InvtID=s.InvtID)

PS. The tables I talk about are views...

Thanks a lot


________________
Magda Banuet

 

[marsh]

I think it's because of your where clause -- once it is applied, the null results disappear. You might try something like this:

SELECT i.InvtID, i.SiteID, i.QtyAvail,
s.InvtID as InvtID2, s.SiteID as SiteID2, s.InvcDate
FROM Inventory i FULL OUTER JOIN Sales s ON (i.SiteID=s.SiteID) AND (i.InvtID=s.InvtID)
and s.InvcDate>= '2003-12-01 00:00:00' AND s.InvcDate< '2003-12-27 00:00:00'
where i.QtyAvail is null or s.InvcDate is null
ORDER BY i.ClassID, i.InvtID, i.SiteID

 

[SonOfEmidec1100]

Heres my thoughts, use a left join and with a null check to get those where there are no rows in the Sales table

SELECT i.InvtID, i.SiteID, i.QtyAvail,
s.InvtID as InvtID2, s.SiteID as SiteID2, s.InvcDate
FROM Inventory i LEFT OUTER JOIN Sales s ON (i.SiteID=s.SiteID) AND (i.InvtID=s.InvtID)
WHERE (s.InvcDate>= '2003-12-01 00:00:00' AND s.InvcDate< '2003-12-27 00:00:00') OR s.InvcDate IS NULL
ORDER BY i.ClassID, i.InvtID, i.SiteID