Optional join of tables

[Sai Prasad]

All,
I have three tables INVOICE, WEB_ORDER, ORDER.
INVOICE and WEB_ORDER can be linked using the column "customer_id".
WEB_ORDER and ORDER can be linked using the column "order_id"
ORDER table contains the column "trading_partner" and INVOICE has the other column of interest "invoice_id".
WEB_ORDER is not guaranteed to contain the rows with the same "customer_id"
as in INVOICE table.
I like to get the "invoice_id" (always) and trading_partner (if exists)
by joining the 3 tables. I tried the SQL below:

  
select unique i.invoice_id, o.trading_partner
FROM invoice i, order o, web_order wo 
where wo.order_id=o.order_id(+) and 
wo.customer_id(+)=i.customer_id and

I know my invoice table has 118 rows. When I execute the above select,
I get only 110 rows. How do I get all 118 rows and still query for
trading_partner if exists?
Thanks a lot.

[Mark Spritzler]

Hi Sai. How are you?
This might not work, but what if you remove the unique keyword. How many records do you get then?
Which database are you using? because I thought the keyword is DISTINCT and not UNIQUE. Maybe both work.
Mark