SQL Server working with 2 tables (foreign key?) ASP.NET

Question

I have 2 tables in my SQL Server database, using Visual Studio 2013:

Category
- Id
- Name
Product
- Id
- Name
- CategoryId

I have a Product Page built in ASP.NET (webforms) in which I can Create, Edit or Delete Products.

I made a connection (foreign key) between the 2 tables.

I want to display my products and instead of having the Category as a number (Id) I want to have it`s name.

How should I do it?

My knowledge in SQL is pretty basic.

This is how I`m displaying the code, using a DataList:

string SQL = "SELECT * FROM Products;";
SqlCommand cmd = new SqlCommand(SQL, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);

dlProducts.DataSource = ds;
dlProducts.DataBind();

Thank you a lot!!

score 2 · Answer 1 · answered Nov 03 '15 at 19:15

2

Join to the categories table, and return the name from the categories table along with the main result.

string SQL = "SELECT p.*, pc.Name FROM Products p
              INNER JOIN ProductCategories pc
              ON p.ProductCategoryID = pc.ProductCategoryID;";

answered Nov 03 '15 at 19:15

Brian Mains

50,520
35
148
257

I want to thank the all of you guys! Thank you alot really!! – Peter Nov 03 '15 at 19:20

vacsora · Answer 2 · 2015-11-03T19:39:16.717

1

Change your select statement into this:

"SELECT p.Id, p.Name, c.Name FROM Product p, Category c WHERE p.CategoryId=c.Id;"

edited Nov 03 '15 at 19:39

answered Nov 03 '15 at 19:14

vacsora

222
1
7

while this looks like it would technically work, I believe comma "joins" are generally frowned upon as they are an older syntax and inefficient. See http://stackoverflow.com/a/129410/2312877 for more info. Though there is some debate on the inneficiency of it as well :) – Kritner Nov 03 '15 at 19:28

score 1 · Accepted Answer · answered Nov 03 '15 at 19:15

You're looking for a join.

Your query could look like this:

select *
from products p -- p is an "alias" for the table products
inner join category c on p.categoryId = c.id -- here we're specifying that the products table to join onto the category table via their relationship columns.

Note the above will return more columns than what you require, you can change the * to only return the columns needed (and it's normally considered better to specify a column list)

SQL Server working with 2 tables (foreign key?) ASP.NET

3 Answers3