CODEWITHSUNDEEP
c#sql.netsql-server
user9984958
user9984958

Reputation:

Retrieve data from different tables

I want to retrieve data from two tables like below. I have a Products table which has P_id, P_name columns and a BATCH table with p_id_fk as a foreign key to the Products table.

This is my query; I want to retrieved from product's name from the Product table because I have stored the Products table primary key as a foreign in the Batch table.

SqlDataAdapter sda = new SqlDataAdapter("Select batch_id, quantity, left_qty, purchaseDate, manufacturing_date, expiryDate from batch where Convert(DATE, expiryDate, 103) BETWEEN @from AND @to", con);

sda.SelectCommand.Parameters.AddWithValue("@from", Convert.ToDateTime(datePicker1.SelectedDate.Value).ToString("yyyyMMdd"));
sda.SelectCommand.Parameters.AddWithValue("@to", Convert.ToDateTime(datePicker2.SelectedDate.Value).ToString("yyyyMMdd"));

Upvotes: 2

Views: 93

Answers (3)

Arnoldas Bendoraitis
Arnoldas Bendoraitis

Reputation: 1065

Not sure if I understood your question correctly, but I believe for your query you are looking for something simple as JOIN between Products and Batch tables:

SELECT
    P.P_id,
    P.P_name,
    B.batch_id,
    B.product_name,
    B.quantity,
    B.left_qty,
    B.purchaseDate,
    B.manufacturing_date,
    B.expiryDate
FROM Batch AS B
INNER JOIN Products AS P
ON B.p_id_fk = P.P_id
WHERE CONVERT(DATE, B.expiryDate, 103) BETWEEN @from AND @to

p_id_fk name you provided might be not an actual column name in Batch table but rather the name of the foreign key constraint itself as it appears by the naming convention (_fk suffix).

Upvotes: 1

Barr J
Barr J

Reputation: 10929

you need to have a join or cross apply here.

Option 1 - inner join: 

    Select         
    b.batch_id,pd.product_name,quantity,left_qty,
    purchaseDate,manufacturing_date,expiryDate from batch b 
    inner join product pd on pd.p_id = b.p_id  where Convert(DATE,expiryDate,103) 
    BETWEEN @from AND @to

Option 2 cross apply:

    Select         
    b.batch_id,pd.product_name,quantity,left_qty,
    purchaseDate,manufacturing_date,expiryDate from batch b 
    cross apply
    (
     select product_name from product p
     where p.p_id = b.p_id
    ) pd
    where Convert(DATE,expiryDate,103) 
    BETWEEN @from AND @to

for more about cross apply look here.

Upvotes: 1

s.Morley
s.Morley

Reputation: 213

If you want to retrieve data from two tables you need to use a SQL JOIN

I am not sure of the exact make up of your tables but something like the below

Select batch_id,
product_name,
quantity,
left_qty,
purchaseDate,
manufacturing_date,
expiryDate
from batch B
INNER JOIN Products P
ON P.P_id = B.P_id
where Convert(DATE,expiryDate,103) BETWEEN @from AND @to

Upvotes: 2

Related Questions