What am I missing in outer join query

  • Thread starter Thread starter Sunny
  • Start date Start date
S

Sunny

I am having problem with outer join in query. I am sure I am missing
something and I am sure someone will point me the right direction.

I have 2 tables

AreaOffice
Purchases

I want all the records from Areaoffice and related purchases from Purchases
table.
AreaOffice:
AOId AOName
1 AreaI
2 AreaII
3 AreaIII

Purchases:
AOId Date Amount
1 7/1/04 100
1 7/2/04 200
3 7/1/04 300

What I want is
AOId AOName Date Amount
1 AreaI 7/1/04 100
1 AreaI 7/2/04 200
2 AreaII
3 AreaIII 7/1/04 300

But I dont get AreaII record on final result. Here is my query:

SELECT AreaOffice.AoId, AreaOffice.AOName, Purchases.Date, Purchases.Amount
;
from Areaoffice LEFT OUTER JOIN purchases on purchases.aoid =
areaoffice.aoid ;
order by ao_id

Why am I not getting expected records?

Thanks.
 
Hi Sunny, what is the actual SQL in your query (it's certainly not what you
posted in your message)?

Try deleting the word OUTER in the FROM clause

SELECT AreaOffice.AoId, AreaOffice.AOName, Purchases.Date, Purchases.Amount
from Areaoffice LEFT JOIN purchases on purchases.aoid =areaoffice.aoid
order by ao_id

Hope this helps, Graeme.
 
Hi Sunny,

Well you included Access newsgroup,
so *in Access only* recreating your
tables and sample data, the following query

SELECT
AreaOffice.AOId,
AreaOffice.AOName,
Purchases.[Date],
Purchases.Amount
FROM AreaOffice LEFT JOIN Purchases
ON AreaOffice.AOId = Purchases.AOId
ORDER BY AreaOffice.AOId;

produces exactly what you want

AOId AOName [Date] Amount
1 AreaI 7/1/2004 100
1 AreaI 7/2/2004 200
2 AreaII
3 AreaIII 7/1/2004 300
 
Hi Sunny,

The SQL statement is probably right, but it will return .NULL. value if
there is no sales in some areas. Your should put in NVL function.
SELECT AreaOffice.AoId, AreaOffice.AOName, NVL(Purchases.Date,{}) ,
NVL(Purchases.Amount,0) ;
from Areaoffice LEFT OUTER JOIN purchases on purchases.aoid =
areaoffice.aoid ;
order by areaoffice.aoid
The order clause "order by ao_id" should be "order by areaoffice.aoid" or
simply "order by 1".

Matthew
 
CREATE CURSOR AreaOffice (AOId i,AOName C(10))
INSERT INTO AREAOFFICE VALUES (1 , 'AreaI')
INSERT INTO AREAOFFICE VALUES (2 , 'AreaII')
INSERT INTO AREAOFFICE VALUES (3 , 'AreaIII')

CREATE CURSOR Purchases (AOId i, Date d, Amount y)
INSERT INTO Purchases VALUES (1 , DATE(2004,7,1), 100)
INSERT INTO Purchases VALUES (1 , DATE(2004,7,2) , 200)
INSERT INTO Purchases VALUES (1 , DATE(2004,7,3 ) , 300)

SELECT AreaOffice.AoId, AreaOffice.AOName, ;
Purchases.Date, Purchases.Amount;
FROM Areaoffice ;
LEFT OUTER JOIN Purchases ;
ON Purchases.aoid =Areaoffice.aoid ;
ORDER BY AreaOffice.aoid

-Anders
 
Back
Top