C # SELECT statement, SQL query

Question

I try to get all products from a table Productsand at the same time retrieve the table Company_Namefrom Company. The common column in my table is 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 shows all the products of a particular company.

I need to show all the products.

Answer 1

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

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

This returns all products associated with a valid company or not.

I think you also need to look at your data and check if your foreign keys and correct data are set correctly.

Answer 2

LEFT JOIN, Products. LEFT JOIN , .

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

Answer 3

try it

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 products with its associated company, if any, NULLwill be displayed under Company.Company_Nameotherwise

Answer 4

Left connection is the best solution for you.

Or you can make one function of defining a user, and from there you can get the name of the company, as shown 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