CODEWITHSUNDEEP
sqlsql-server-2008
user123456
user123456

Reputation: 2659

How to Select Data From Multiple Tables using inner join statment?

I have three tables

Products Table :

ID GeneralStockEnabled RetailerID

Sources Table

ID Name RetailerID

and

ProductInventory Table

ProductID SourceID Stock

The user will pass both the @RetailerID and @ProductID in my Stored Procedure.

How Can I select All the sources for particular retailer and attach the stock value coming from the product inventory table to those sources exists in the product inventory table for a particular product id and also select the value of GeneralStockEnabled for that product? . Even I my product has no stocks, I still want to be able to retrieve all the sources for that retailer?.

Any Help is appreciated.

I have this SQL right now :

SELECT S.ID AS SourceID,S.Name AS SourceName,PIN.Stock
FROM Sources S
     LEFT OUTER JOIN ProductInventory PIN
     ON (S.ID = PIN.SourceID)
WHERE S.RetailerID = 1
AND PIN.ProductID = 1

but since my product inventory table has no records now. It is not selecting the left part which are the sources in this case.

Upvotes: 0

Views: 2341

Answers (2)

PrfctByDsgn
PrfctByDsgn

Reputation: 1050

try something like this:

select s.*, pr.Stock, p.GeneralStockEnabled
    from sources s join
        Products p on s.RetailerId = p.RetailerId left outer join
        ProductInventory pr on pr.ProductId = p.Id
    where s.RetailerId = @RetailerId and p.id = @ProductId

Upvotes: 2

Rajdeep Singh
Rajdeep Singh

Reputation: 17834

You can use joins for the same

http://www.w3schools.com/sql/sql_join.asp

Upvotes: 0

Related Questions