What am I missing in outer join query

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.

Graeme
Fri Jul 30 16:02:18 CDT 2004

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.

Sunny
Fri Jul 30 16:38:49 CDT 2004

Graeme,

I tried without outer but gave me same results.


"Graeme Richardson" <graeme@wave_nospam.co.nz> wrote in message
news:ceed0o$2fm$1@news.wave.co.nz...
> 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.
>
>

Gary
Fri Jul 30 20:06:58 CDT 2004

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



"Sunny" wrote:
> 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.
>
>

Matthew
Fri Jul 30 20:48:52 CDT 2004

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



"Sunny" <sunny_1178@hotmail.com> ¦b¶l¥ó
news:Oy3Dm3ldEHA.1952@TK2MSFTNGP11.phx.gbl ¤¤¼¶¼g...
> 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.
>
>

Anders
Sat Jul 31 03:21:52 CDT 2004

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

"Sunny" <sunny_1178@hotmail.com> skrev i meddelandet
news:Oy3Dm3ldEHA.1952@TK2MSFTNGP11.phx.gbl...
> 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.
>
>