How to read the data from three tables which is not having the same id

Question

Hi i am using three table to display the customer order information

1. Order table(OrderId, ItemName, Description, etc...)
2. Order_Gas_Quantity(Order_gas_quantyID, OrderID, Quantity)
3. Customer_Supplying_Days(SupplyingID, CustID, SupplyingDays)

While saving customer data I'm saving the supplying days into customer_Suppling_Days, in this table I am using CustID. Also, tables 1 & 2 have the same ID of OrderID.

I want to display the items which is ordered today and also supplying days. For example, while saving the customer data there is an option to select the days, if customer selecting needs it on Monday, Wednesday, Friday. So the application should generate the report for orders taken today and also item should supply on Friday. This table structure for customer_Suppling_Days

`SupplyDayID    int Unchecked
CustID          int Checked
SupplyDays    nvarchar(50)  Checked

Table Structure for Orders

OrderID int Unchecked
Date    varchar(50) Checked
CName   varchar(50) Checked
CustId  int Checked
CLocation   varchar(50) Checked
Gas varchar(50) Checked
Quantity    varchar(50) Checked
VehicleNo   varchar(50) Checked
VehicleLocation varchar(50) Checked
Source  varchar(50) Checked

Table Structure For Order_Gas_qty

OrderGasQty int Unchecked
CustId  int Checked
GasName varchar(50) Checked
Quantity    varchar(50) Checked
OrderDate   varchar(50) Checked
CylinderNo  varchar(50) Checked
OrderId int Checked

Answer 1

You have stored dates in varchar() format which is strongly not recomended. Store dates in Date format only . In case you cannot change the table structure then simple inner join like following should work:

select O.OrderID,
O.Date,
O.CName,
O.Gas,
O.Quantity,
O.VehicleNo,
O.VehicleLocation,
O.[Source],
OGQ.CylinderNo
from Orders O
inner join Order_Gas_qty OGQ on OGQ.OrderId = O.OrderId
inner join Customer_Supplying_Days CSD on O.CustId = CSD.CustId
where O.Date = convert( varchar(20),getdate(),101) 
-- please change the format here as per your requirement
and SupplyDayID = Day(getdate())
-- this condition also depends on data in your table