C# SELECT statement, SQL query

Question

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.

Your query is correct. Confirm that you have more than one product in your table — Tarik
– Tarik, Commented Aug 9, 2016 at 6:02
Your query seems correct. Maybe you have only one company ? If there are products without company, left join will solve the problem. — Doruk
– Doruk, Commented Aug 9, 2016 at 6:03
@SlavaUtesinov No need for left join. Unless there are products with no company. — Tarik
– Tarik, Commented Aug 9, 2016 at 6:04
inner join has sense " having company key (i.e. not with empty company) — Jacek Cz
– Jacek Cz, Commented Aug 9, 2016 at 6:47

Zein Makki · Accepted Answer · 2016-08-09 06:02:37Z

3

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.

answered Aug 9, 2016 at 6:02

Zein Makki

30.2k6 gold badges58 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gilad Green · Accepted Answer · 2016-08-09 06:08:36Z

2

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

edited Aug 9, 2016 at 6:08

Gilad Green

37.3k7 gold badges67 silver badges99 bronze badges

answered Aug 9, 2016 at 6:05

Kalu Singh Rao

1,6971 gold badge16 silver badges21 bronze badges

Comments

marc_s · Accepted Answer · 2016-08-09 06:41:19Z

0

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

edited Aug 9, 2016 at 6:41

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 9, 2016 at 6:11

Hitesh Prajapati

2,8828 gold badges25 silver badges29 bronze badges

1 Comment

Hitesh Prajapati Over a year ago

@Bella WC ,if possible please mark as select answer.

marc_s · Accepted Answer · 2016-08-09 06:41:37Z

0

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

edited Aug 9, 2016 at 6:41

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 9, 2016 at 6:04

ydoow

2,9964 gold badges27 silver badges42 bronze badges

Collectives™ on Stack Overflow

C# SELECT statement, SQL query

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related