Inner Join on 3 tables SQL server 2017 [duplicate]

Question

I have three tables 1. Ordertable 2. Bill 3. Customers

In Ordertable I have following columns

(*) represents Primary key here

| Customer ID*c| Customer Name | Address | Contact Number | Email|

In Order table I have following columns

| Order ID* | Order Status | Delivery Date | Manager ID | Customer ID| Salesmanager ID| Where Order_status can Containt ‘pending’ for pending order And ‘completed ’ for for completed order

In bill table I have these columns

| Bill ID*| Order ID | Date | Amount |

Now I want to create Query that display completed Order data with inner join on these three as these rules

1. Order_ID as OID from order table
2. Order_Status as Status from order table
3. Delivery_Date as Date from order table
4. Customer_name as Name from customer table
5. Amount as Bill Amount from Bill table What I tries is this Query

select order_status Status,Order_ID OID,Delivery_date Date
from ordertable
inner join bill ON ordertable.order_id = bill.Order_id

and I got this error
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Order_ID'.

I know that order_id is in both tables but how I can solve this And also when I remove it error gone but in the result I see only two columns for Status and Date but not OID and any data from bill how I can do this ?

Answer 1

select order.order_status AS Status, order.Order_ID AS OID, order.Delivery_date AS [Date]
from order
inner join bill ON order.order_id = bill.Order_id

Answer 2

SELECT
ordertable.Order_ID AS OID,
ordertable.Order_Status AS Status,
ordertable.Delivery_Date AS Date,
customer.Customer_Name AS Name,
bill.Amount AS [Bill Amount]
FROM ordertable
INNER JOIN bill ON ordertable.Order_ID = bill.Order_ID
INNER JOIN customer ON ordertable.Customer_ID = customer.Customer_ID
WHERE ordertable.Order_status = 'completed';

Notes: You can also alias table names, same as renaming the columns in result sets, if typing out the entire table name in front of fields is becoming cumbersome. For example "FROM ordertable AS ot" or simply "FROM ordertable ot" and then you can simply refer to it as ot.fieldname.

You are ambiguous on whether your field names have underscores or actual whitespace (spaces). If your fields have spaces in them, you must quote them in square brackets. eg. Order_ID versus [Order ID] works.