Re: What am I missing in outer join query

• Messages sorted by: [ date ] [ thread ]

Date: Sat, 31 Jul 2004 10:21:52 +0200

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.
>
>

• Messages sorted by: [ date ] [ thread ]