OUTER JOIN problem in SQL

[Gragi]

Hi Friends,
I have written a SQL informix
but somehow because of my outer join i'm getting double records
how can I avoid that

here is the sample code i have written

(SELECT DISTINCT
B.itemid,
A.ordernumber,
A.orderdate,
(SELECT COUNT(BB.itemid) from table2 BB where BB.ordernumber = A.ordernumber and BB.type = 'HOTEL' GROUP BY B.ordernumber) HotelCnt,
(SELECT DD.city from table4 DD where DD.link = C.link) City
B.type
FROM table1 A,table2 B,table3 C
OUTER table4 D
WHERE A.date between '08/01/2001' and '08/31/2001'
AND A.ordernumber = B.ordernumber
AND B.itemid = C.itemid
AND C.link = D.link
AND B.type in ('HOTEL','CAR')
)

I'm getting results like :

itemid ordernumber orderdate HotelCnt City Type
120 1001 2001-08-01 4 CAR
124 1009 2001-08-01 3 HOTEL
124 1009 2001-08-01 3 LAX HOTEL
139 1010 2001-08-01 3 HOTEL
139 1010 2001-08-01 3 WIS HOTEL

My result should be like :
itemid ordernumber orderdate HotelCnt City Type
120 1001 2001-08-01 4 CAR
124 1009 2001-08-01 3 LAX HOTEL
139 1010 2001-08-01 3 WIS HOTEL
Means Type HOTEL should return only which has City.
Thanks for your help in advance,
Gragi

 

[Gragi]

Hi Friends,
Finally I found the solution for OUTER JOIN problem.

My previous code was
(SELECT DISTINCT
B.itemid,
A.ordernumber,
A.orderdate,
(SELECT COUNT(BB.itemid) from table2 BB where BB.ordernumber = A.ordernumber and BB.type = 'HOTEL' GROUP BY B.ordernumber) HotelCnt,
(SELECT DD.city from table4 DD where DD.link = C.link) City,
B.type
FROM table1 A,table2 B,table3 C
OUTER table4 D
WHERE A.date between '08/01/2001' and '08/31/2001'
AND A.ordernumber = B.ordernumber
AND B.itemid = C.itemid
AND C.link = D.link
)

I just changed 'FROM' section to
FROM table1 A,table2 B,
OUTER (table4 D, OUTER table3 C)

That's it - it's working fine...
I found these different types of OUTER JOINS in

http://database.sarang.net/database.../iiug/ifaqr.htm

This link is awesome.

Hope this helps.....
Gragi