SQL连接表和内连接？

I have 3 tables. Products, Steps and Pictures.

我有3张桌子。产品,步骤和图片。

Products Fields: Id, Name

产品领域:Id,Name

Steps Fields: Id, ProductId, Description

步骤字段:Id,ProductId,Description

Pictures Fields: Id, StepId, Picture

图片字段:Id,StepId,图片

I'm working with Microsoft Visual Studio and Razor currently I'm using for example UrlData to load the correct tables and fields in the db. www.example.com/Id=1 (Shows product #1)

我正在使用Microsoft Visual Studio和Razor,我正在使用例如UrlData来加载数据库中正确的表和字段。 www.example.com/Id=1(显示产品#1)

var productId = UrlData[0].AsInt();
var product = db.QuerySingle("SELECT * FROM Steps WHERE Id = @0", productId);

I have successfully been able to pull the data I wanted to display but the problem now is im using a very simple +1 UrlData or -1 UrlData to go to the next step or backwards. Obviously this will not work if a step is deleted and the website will not stop at the end of the steps for a product.

我已经成功地提取了我想要显示的数据,但现在的问题是使用非常简单的+1 UrlData或-1 UrlData来进行下一步或后退。显然,如果删除一个步骤并且网站不会在产品的步骤结束时停止,这将不起作用。

I'm at the point now I believe I will need to use Join Tables I will need to display all the data above from the 3 tables by using UrlData.

我现在点我相信我需要使用连接表我需要使用UrlData显示3个表中的所有数据。

I have created a test join table ex: "joinTest" Fields: Id, PictureId, StepId, ProductId

我创建了一个测试连接表ex:“joinTest”字段:Id,PictureId,StepId,ProductId

How would I query this table to display all of the data above in the first paragraph?

如何查询此表以显示第一段中的上述所有数据?

More examples of my razor queries:

我的剃刀查询的更多示例:

string description = product.description;
var step = db.QuerySingle("SELECT * FROM Products WHERE Id = @0", product.ProductId);
var steps = db.Query("SELECT Id, StepName FROM Steps WHERE ProductId = @0", productId).ToList();
var photos = db.Query("SELECT Id, FileTitle FROM Pictures WHERE StepId = @0", productId).ToList();

2 个解决方案

#1

You don't need to add a new table for this. Just a new field NextStepId or StepSeqNum to the existing Steps table. You also need to add the current step to the Url.

您无需为此添加新表。只是一个新字段NextStepId或StepSeqNum到现有Steps表。您还需要将当前步骤添加到Url。

#2

Humm if you just want to display data from the 3 tables, why you created a third table? Just do:

如果您只想显示3个表中的数据,为什么要创建第三个表?做就是了:

select products.name as Product, steps.description as Description, pictures.filetitle as MyPicture   
from pictures
join steps on steps.id=pictures.stepid
join products on products.id=steps.productid 
where steps.id=@yourvariable

But i dont think this will solve the problem with +1 or -1, actually i cant see a relation beetween that and join command!

但我不认为这将解决+1或-1的问题,实际上我无法看到该和join命令之间的关系!

Is not better to load an array with all available ids before changing page ?

在更改页面之前加载包含所有可用ID的数组不是更好吗?

You can specify any column and you will see all the data you want.

您可以指定任何列,您将看到所需的所有数据。

Is that you wanted?

那是你想要的吗?

#1