The answer to this question depends on what result you are trying to achieve. You don't get into whether or not an Order has at least one Order-1 record, and whether or not an Order-1 has at least one Order-2 record. I am going to assume that what you mean is to get all orders, related order-1 records if there are any, and related order-2 records if there are any. In which case, your SQL will look like this:

SELECT o.order_no,o.order_date,o,cust_code,o1.stock_no,o1.color_id,o1.order_qty,
o1.orderprice,o2.deli_qty,o2.deli_date
FROM order o
LEFT OUTER JOIN order1 o1
ON o.order_no=o1.order_no
LEFT OUTER JOIN order2 o2
ON o1.order_no+o1.stock_no+o1.color_id=o2.order_no+o2.stock_no+o2.color_id

However, there are several issues with this. First of all, the use of the composite key in the relation between o1 and o2 could be problematic. You don't make clear what the data types are for each field, and whether or not the expression can be concatenated. If not, then you will have to separate the three fields (e1=e1,e2=e2,e3=e3) which could present a performance problem. I would suggest giving o1 a unique key of it's own and storing that key in o2. Secondly, remember that you will get .NULL. values for the o1 and o2 fields if no records exist for o in o1, and likewise with o1 and o2.

If you are only looking for records where o,o1, and o2 all exist then you can use an INNER JOIN.

Hope this helps.

>Hi Ken,
>
>Thanks for your reply.
>
>Let me describe the relation between these three tables (Order, Order-1 and Order-2) in more details.
>
>Order (Order-No, Order-Date, Cust-Code,...)
> Key = Order-No
>Order-1 (Order-No, Stock-No, Color-ID, Order-Qty, Order-Price,...)
> Key = Order-No
>Order-2 (Order-No, Stock-No, Color-ID, Deli-Qty, Deli-Date,...)
> Key = Order-No + Stock-No + Color-ID
>
>Each Order can have more than one Order-1 records.
>Each Order-1 can have more than one Order-2 records.
>
>I want to join these three tables to form a new table which contain the following attributes:-
> (Order-No, Order-Date, Cust-Code, Stock-No, Color-ID, Order-Qty, Order-Price, Deli-Qty, Deli-Date,...)
>
>I want to use the Select - Join statement of join those three tables.
>Order table is used the key (Order-No) to join with the Order-1 table.
>Order-1 table is used the key (Order-No+Stock-No+Color-ID) to join with the Order-2 table.
>
>And i want to know how to join these three tables.
>
>Thanks for your assistance.
>
>Dennis