CODEWITHSUNDEEP
c#sql
Bella
Bella

Reputation: 43

C# SELECT statement, SQL query

I am trying to get all the products from the Products table, and at the same time retrieve Company_Name from Company table. A common column in both my table is the Company_Id.

I am using this query: 

SELECT 
    products.product_id, 
    products.product_name, 
    products.product_desc, 
    products.unit_price, 
    products.stock_level, 
    products.product_image, 
    products.gender, 
    products.type_of_acct, 
    products.product_cname, 
    products.product_cdesc, 
    products.company_id, 
    company.company_name 
FROM
    products 
INNER JOIN 
    company ON products.company_id = company.company_id

However this only show all the products from a specific company.

I need to show all the products.

Upvotes: 4

Views: 142

Answers (4)

ydoow
ydoow

Reputation: 2996

Try this

SELECT 
    Products.Product_ID, Products.Product_Name, Products.Product_Desc,
    Products.Unit_Price, Products.Stock_Level, Products.Product_Image,    
    Products.Gender, Products.Type_Of_Acct, Products.Product_CName, 
    Products.Product_CDesc, Products.Company_Id, Company.Company_Name 
FROM
    Products 
LEFT JOIN 
    Company ON Products.Company_Id = Company.Company_Id

This will return you all the products, with its linked company if any, a NULL will be shown under Company.Company_Name otherwise

Upvotes: 0

Hitesh Prajapati
Hitesh Prajapati

Reputation: 2842

Left join is best solution for you.

Or you can make one user define function and from there you can retrieve company name like below

SELECT   
    Products.Product_ID, Products.Product_Name, 
    Products.Product_Desc, Products.Unit_Price, 
    Products.Stock_Level, Products.Product_Image,     
    Products.Gender, Products.Type_Of_Acct, 
    Products.Product_CName, Products.Product_CDesc, 
    Products.Company_Id, 
    dbo.GetCompanyNameFromCompanyID(Products.Company_Id) AS Company_Name    
FROM 
    Products

Upvotes: 0

Kalu Singh Rao
Kalu Singh Rao

Reputation: 1697

You can use LEFT JOIN to get all details from Products table. LEFT JOIN is fetch all records from left table and also fetch matching records from right table.

SELECT Products.Product_ID, Products.Product_Name, Products.Product_Desc, Products.Unit_Price, Products.Stock_Level, Products.Product_Image, Products.Gender, Products.Type_Of_Acct, Products.Product_CName, Products.Product_CDesc, Products.Company_Id, Company.Company_Name 
FROM Products 
LEFT JOIN Company ON Products.Company_Id = Company.Company_Id

Upvotes: 2

Zein Makki
Zein Makki

Reputation: 30022

It seems you have an optional relationship here, so use LEFT JOIN:

....
FROM Products 
LEFT JOIN Company 
ON Products.Company_Id = Company.Company_Id

This retrieves all the products whether linked to a valid company or not.

I think you also need to go over your data and check if you have your foreign keys set up right and have the correct data.

Upvotes: 3

Related Questions